Vince, your code seems to have corrected the issue in a test copy (now I have to compare and see why...you have a good amount of information there) to answer the question about the objectives: the ultimate objective is for me to have a program that will, once supplied with a library parameter, generate a large chunk of table level data dictionary & research information (stuff like % populated, min, max, format,informat, continuous or discrete values and a few other things.). The idea was to make use of SAS metadata and work it as follows: (apologies for poor formatting) Step Action 1 get count of tables in library 2 Loop 1 begins 3 put table names into variable 4 loop 1 ends 5 Loop 2 begins 6 put count of columns into variable 7 Loop 3 begins 8 put column names into variable 9 loop 4 10 get count of obs by column 11 Pass count to a table 12 Loop 4 ends 13 Loop 3 ends 14 Loop 2 ends here is the full macro in it's unbridled, horrible, noobness (and yes I know it is bad, I'm working on it!!!) Notes: This macro uses some debugging code taken from Quentin McMullen: http://www.bi-notes.com/2012/10/sas-code-show-sas-macro-variables/?goback=%2Egde_85005_member_174589592#%21 There are MANY errors/bugs, particularly later in the macro, the reason I posted the question is to start learning what I did wrong, and fixing it I have not, in the copy below, incorporated the change reccomended by Vince Per Reeza's comment I need to investigate getting the column count from the metadata The macro: %macro Step1(lib,debug=1); %local colcount; /* The first SQL procedure obtains the count for the second SQL procedure */ proc sql noprint; select count(*) into :count from dictionary.tables where upcase(libname)=%upcase("&lib"); quit; %put("Step1 completed"); %do i=1 %to &count while i>= &count; /* The second SQL procedure obtains the memname variable */ proc sql noprint; select upcase(memname) into :dsname1 - :dsname%TRIM(%LEFT(&count)) from dictionary.tables where upcase(libname)=%upcase("&lib"); quit; %local tablecount ; %let tablecount= %TRIM(%LEFT(&count)); %end; %put("Step 2 completed"); /* Section below gets the count of columns in each dataset*/ %do k=1 %to &tablecount; proc sql noprint; select count(*) into :countCol%TRIM(%LEFT(&count)) From &lib..%upcase(&&dsname&k) ; quit; %put("below") /*unresolved reference here*/ %let colcount= &&countCol%TRIM(%LEFT(&count)); %*If in debug mode, dump local macro vars to log; %if &debug=1 %then %do; %printMacVars(scope = Step1) %end; %put("above"); %local Tbl; %let tbl = %upcase("&&dsname&k"); %do y=1 %to &colcount; /*Cname is also unresolved but oddly still gets the values into the macro variables*/ proc sql feedback noprint; select upcase(name) into :cname1 - :cname%trim(%left(&colcount)) from &lib..%upcase(&&dsname&k) ; quit; proc sql feedback noprint; select count("&cname&y") into :countresult1 from &lib..%upcase(&&dsname&k) ; quit; %local ccol; %let ccol = %upcase("&&cname&y"); /* This point and up works*/ proc sql feedback; update %upcase(&lib..test) set counts = &countresult1 where %upcase(&lib..name) = %upcase(&ccol) ; quit; %end; %end; %put("step 3 ends here"); %mend Step1; Message was edited by: Mike Montgomery
copy/paste error
... View more
