Change the format used to convert the character string to a number in the INPUT function call. For formats and informats the number before the period is the total length and the number after the period is the number of decimal places.  But for informats if the text has a decimal in it then it will use it when reading the data and ignores the number of decimals when in the informat. Art's code uses 4.0 as the informat which will handle character strings up to 4 characters long. Your code uses 1.0 which will handle only a single character. I would recommend using 12. as the informat which is the normal length SAS uses for auto conversion between text and numbers. Read up about formats and informats in the manuals. ... View more

If you really have a series of Excel files to import do they all have the same variables? And if your main variable type issue is caused by EMPTY columns then you should be able just identify and drop the missing columns. Step one create a dataset with the preferred structure that you can use as a template (or even a master dataset). Step two import the next file. Step three identify the empty columns.  Look at this discussion for example: http://communities.sas.com/message/12474#12474 Step four convert to the standard structure with code like this: data next_fixed; * The next line will cause SAS to create the variables as defined in TEMPLATE but not actually include any data as 0 is always false;   if 0 then set template;    set next (drop=&missingvars); run; ... View more

I usually avoid trying to loop through observations and instead apply SAS operations to full sets. You can pull the metadata for your original file and use it to generate the new variables. You can view your conversion step as this simple data step. data want;   &attrib;   array _char $32 &oldchar ;   array _num &newnum;   set have (rename=(&rename)) ;   do over _char;     _num=input(_char,best32.);   end;   drop &oldchar ; run; Where ATTRIB contains a series of ATTRIB statements to set the variables names, type, length, format, label etc . RENAME contains old=newname pairs that will rename the old character variables to unique temporary names so that new numeric variables with those names can be created. OLDCHAR contain the renamed character variables that need to be converted to numeric. NEWNUM contain the names of the variables to convert from numeric to character. For example if you want to convert all character variables to numeric then you can generate those four list variables with something like: proc sql noprint ;   create table vardef as select * from dictionary.columns     where libname='WORK' and memname='A'   ;   select catx(' ','attrib',name,'length=8','label=',quote(trim(label)||' '))        , varnum     into :attrib separated by ';'        , :dummy     from vardef     order by varnum   ;   select name        , catx('=',name,'_'||name)        , '_'||name     into :newnum separated by ' '        , :rename separated by ' '        , :oldchar separated by ' '     from vardef   ; quit; For a more complicated situation you might need to use more complicated queries to generate ATTRIB (especially for length and format attributes) or to subset the variables included in the other three macro variables. ... View more

The reason you might have to resort to using macro logic to generate SAS code is that the LAG functions do not take the lag distance as an arguemet, but instead it is part of the actual function name.  That is why you cannot use the data step variable I to control the lag distance. If you want to create 12 lagged variables and the number 12 is relatively constant it is probably easier to just use your editor to generate the 12 lines. So what is your actual problem?  Perhaps there is a better way to handle it? ... View more

Macro is text generating tool.  Most macros generate text that looks like SAS code so that it can be passed to regular SAS for interpretation as as SAS statements. Your first version generates these statements. MPRINT(A):   data a; MPRINT(A):   set b; MPRINT(A):   var1=lag1(var1); MPRINT(A):   var2=lag2(var1); MPRINT(A):   var3=lag3(var1); MPRINT(A):   var4=lag4(var1); MPRINT(A):   var5=lag5(var1); MPRINT(A):   var6=lag6(var1); MPRINT(A):   var7=lag7(var1); MPRINT(A):   var8=lag8(var1); MPRINT(A):   var9=lag9(var1); MPRINT(A):   var10=lag10(var1); MPRINT(A):   var11=lag11(var1); MPRINT(A):   var12=lag12(var1); MPRINT(A):   run; Your second version has no looping in the macro language and so will only generate one statement. And since the macro progressing finishes before the generated data step runs your code is the equivalent of this: %let n=0; %let n=%eval(&n+1); data a;   set b;   do i=1 to 12;     var&n=lag&n(var1);   end; run; Which is the same as: data a;   set b;   do i=1 to 12;     var1=lag1(var1);   end; run; ... View more

I see two problems with that.  1) From the error message it looks like the variable ViewName in the table LogicTable is numeric and missing so CALL SYMPUT is setting macro variabel ViewNames to a dot to represent a missing value.  To see what the macro variable ViewNames received throw in a %PUT "&viewnames"; statement. 2) Unless the table LogicTable has only one observation you cannot use CALL SYMPUT to generate a list. To generate a list into a macro variable it is easiest to use PROC SQL. proc sql noprint ;    select viewname into :viewnames separated by ' ' from logictable; quit; Note that the separated by clause is critical or else PROC SQL will just give you the value from the first observation if selects. ... View more

I am not sure I understand this requirement.  Are you saying that if a panelist did not report an income for a year you want to treat that as $0? Another method that I like to use that requires fewer copies of the data is create a selection criteria that can be used in a where statement. So for your example I would get a list of id values and use it to subset the data. proc sql noprint ;    select distinct id into :id2009 separate by ' ' from have where year=2009; quit; proc means data=have;    where id not in (&id2009);    var income; run; ... View more

Test for how to append to empty (zero observation) master datasets and also make sure you know what happens if you do not specify values for all variables. In general you will find that this type of technique is not normally needed (or desired) when processing data with SAS.  To me it looks more like the solution that someone would propose that was used to using a language that did not really work on datasets (or just SETS like SQL does).  Art's solution using PROC APPEND or other methods to combine your new data into your old data is much more likely to be generally useful. ... View more

You could use the INSERT statement in SQL. create table want as &query1; insert into want &query2; insert into want &query3; .... You could instead create views in SQL and then use a DATA step to combine them. DATA steps do not have the same limits. proc sql; create view v001 as &query1; create view v002 as &query2; .... quit; data want ;   set v001 v002 .... ; run; ... View more