SAS Dates Import issue

2
votes

I am trying to import a csv file into sas using custom code. Below are the sample lines from the raw data.

Item,date,WKLY_QTY,WKLY_SALES
10001,01Apr12,3313,67536.16
10001,15Apr12,889,26577.66
10001,22Apr12,4543,65001.8
10001,29Apr12,2822,74522.02

My SAS Code is as follow:

data LOtpt.Dummy2; 
infile "&InptPath.\Dummy2_CSV.csv" dsd dlm=','  FIRSTOBS=2; 
input Item date DATE7. WKLY_QTY WKLY_SALES; 
run;

Result I am getting is as follow:

Item    date  WKLY_QTY WKLY_SALES
10001   19084   .   3313
10001   19098   .   889
10001   19105   .   4543
10001   19112   .   2822

Can any one please help me with the mistake. There is some problem with date informat because when I am taking this informat as character everything is going good.

2
dateimportsas

2 Answers

3
votes

You're mixing two input varieties here. The only difference is a single colon:

data work.Dummy2; 
infile datalines dsd dlm=','; 
input Item date :DATE7. WKLY_QTY WKLY_SALES; 
datalines;
10001,01Apr12,3313,67536.16
10001,15Apr12,889,26577.66
10001,22Apr12,4543,65001.8
10001,29Apr12,2822,74522.02
;;;;
run;

List input does not normally allow an informat in the list (you can put informats in an informat statement). Modified list input (as shown above) is however permitted.

1
votes

I suggest that you get into the habit of defining all variables used in your data set explicitly using ATTRIB statements. It takes a bit more typing but you end up with code that is much easier to use, especially if you need to get help from other people. Even better, include a KEEP statement to control only the variables needed, which prevents stray variables from showing up.

This has the additional benefit of allowing you the use LIST input where appropriate.

In other words, try this:

data LOtpt.Dummy2; 

  /* Define all variables and attributes here */
  attrib Item       informat=5.      format=5.;
  attrib Date       informat=date7.  format=yymmdd10.;
  attrib Wkly_Qty   informat=best10. format=comma9.;
  attrib Wkly_Sales informat=best10. format=dollar11.2;
  keep Item Date Wkly_Qty Wkly_Sales; 

  infile "&InptPath.\Dummy2_CSV.csv" dsd dlm=',' firstobs=2; 

  input Item Date Wkly_Qty Wkly_Sales; 
run;

If you get in the habit of doing something like this all the time, it gets much easier over time. Note I selected formats and informats based on what I think your data looks like. You should choose ones that best meet your needs.