Then you can write a DATA step to read the data and have complete control.  For example something like this. DATA rawdata;   infile 'myfile.csv' dsd truncover lrecl=30000 firstobs=2 ;   LENGTH straw_wt panicle_wt DateEntered 8  notes $200 height DateEntered2  rep block row col plant 8 line $20 plot_ID_final 8;   informat DateEntered DateEntered2 YYMMDD10.;   format DateEntered DateEntered2 YYMMDD10.;   INPUT straw_wt -- plot_ID_final; RUN; ... View more

Save you data in a text file instead.  You can save from Excel to Tab delimited or CSV delimited file. ... View more

Looks like your JCL is setting SYSPARM to the value NY20150316 ? Is that what you see inside SAS? If so then your global macro variable MYDATE will be set to 20150316 (notice there are no quotes). You then pass this into the local macro variable MYDATE. You then try to pass this into DB2 by using the syntax '&MYDATE', but macro variables do not evaluate inside of single quotes.  So you literally passing '&MYDATE' into DB as the value for the DATE() function call.  I doubt that knows what to do with a string of characters that starts with an ampersand. Here is one way to add the single quotes that I assume DB2 wants into the value of the macro variable: %let mydate=%sysfunc(dequote("'&mydate'")); You can then remove the single quotes in the code you are passing to DB2: DATE(&MYDATE) ... View more

Your example data will work well with FLOWOVER option.  Just as long as each observation has the right number of fields it won't matter which line they are on. data temp ;          infile cards dlm=':' dsd FLOWOVER firstobs = 2 ;          length                   ID           8          FNAME      $20          MNAME      $20          LNAME      $30                  CITY       $40          STATE      $ 2          ZIP        $ 5          PHONE      $15          PREV_CITY  $40          PREV_STATE $ 2          PREV_ZIP   $ 5          PREV_PHONE $15      ;      input  id -- PREV_PHONE ;           cards4; ID:FNAME:MNAME:LNAME:CITY:STATE:ZIP:PHONE:PREV_CITY:PREV_STATE:PREV_ZIP:PREV_PHONE 1:Homer:Jay:Simpson :Springfield:::(222)-333-4444 :Langley Falls:VA:88888:(555)-666-7777 2:Bill:Phil:Williams :New York:NY::(123)-456-7890 ::::(987)-000-1212 ;;;; ... View more

You probably are having trouble with the step that is reading the text into a SAS dataset. To generate the two variable structure used in the solution you can mix list and formatted input modes. Read the lines using comma as a delimiter, but read the RESPONSE variable using a INFORMAT so that it ignores the commas and just reads the values. data response;    infile CARDS dsd dlm=',' truncover;    length store $20 response $60 ;   input store response $60.; cards; Store 1,Bread, Eggs, Juice Store 2,Juice Store 3,Eggs, Juice ;;;; 732  data _null_; 733    set response ; 734    file log dsd ; 735    put store response ; 736  run; Store 1,"Bread, Eggs, Juice" Store 2,Juice Store 3,"Eggs, Juice" ... View more

Your WHERE clause will be different in that case. Instead of looking for variables that end in _C  (where upcase(name) like '%^_C' escape '^')  you will want to look for variables that end in S (where upcase(name) like '%S') . . ... View more

It makes the logic clearer to the novice user.  Also there are potential side effect to querying DICTIONARY tables.  Probably not in this case since both the LIBNAME and MEMNAME values are exact matches. ... View more

Rename is similar to suffix.  You need to query the list of names. proc contents data=have out=contents(keep=name) noprint ; run; proc sql noprint ; select catx('=',name,substr(name,1,length(name)-2))    into :renames separated by ' ' from contents where upcase(name) like '%^_C' escape '^' ; quit; data want ;   set have (rename=(&renames)); run; ... View more

proc freq; tables year*acode / output=want ; run; ... View more

You could use %SCAN() if your data is in that format.  Just use ' and $ as the delimiters.  Note you can code the delimiter list as '$' to avoid unbalanced quotes and the need for macro quoting.  It doesn't matter if an individual delimiter appears more than once in the list. %let sheet1='Jan 2014$'; %let sheet2='Feb 2014$'; %let sheetN=2; %let month1 = Before; %let month2 = Before; %macro sheet2month;   %do i=1 %to &sheetn ;     %let month&i=%scan(&&sheet&i,1,'$') ;   %end; %mend sheet2month; %sheet2month; %put &=sheet1 &=month1; %put &=sheet2 &=month2; ... View more

Your variable already has DATE values.  You just have the YYMMDDN8. format attached to it instead of the SAS standard DATE9. format. So when you print them or use PUT to display the value it looks like a human readable format. A date variable that displays as '19811231' when using the YYMMDDn8. format has the numeric value of 8400.  The INPUT format YYMMDD8. cannot convert '8400' (or the other values in your data) to a valid date. Since you already have a date you can extract the year and month using the YEAR() and MONTH() functions. ... View more