topic Re: Assign values to Macro from SQL Database in SAS Health and Life Sciences

Assign values to Macro from SQL Database

deleted_user — Wed, 19 Dec 2007 16:16:17 GMT

The problem goes like this:
I want to read the values of a variable in a table in SQL Database and each of this value should be given as an input to a SAS Macro which generate sas dataset according to the values given to the Macros, e.g., like if AE is the first value of the table, it should b read from database and give as the input to the Macro. This should b run for every value of the column in the database table.
I use the following code:

%let tbl=;
%macro DATASET;
%let n=1;
%do n %to &ds;
proc sql noprint;
select datasetname format=$10. into :tbl
from datalib.ds_attributes
order by datasetname;
%readtbl(tbl=&tbl);
quit;
%end;
%put &tbl;
%mend DATASET;
%dataset;

I am sure v can do this by assign any do loop function, but It reads only the first value of the variable and runs as many times as &DS, ultimately brings out an error.
Let me know if any solutions.

Re: Assign values to Macro from SQL Database

deleted_user — Wed, 19 Dec 2007 17:07:25 GMT

see a solution for invoking a macro multiple times from a data set, in a parallel forum at
http://support.sas.com/forums/thread.jspa?threadID=2294&tstart=0

The only difference is that your data set uses a library engine pointing into an SQL database

PeterC

Re: Assign values to Macro from SQL Database

deleted_user — Wed, 19 Dec 2007 22:50:29 GMT

Thanks that worked. It was really helpful.

Re: Assign values to Macro from SQL Database

deleted_user — Thu, 27 Dec 2007 21:01:55 GMT

The above solution did worked out, but whenever it find out any error, the set option is set to obs=0. Therefore, no records are read or processed & whatever datasets are created has 0 records...
the error I get is as follows:

MPRINT(READTBL): *PRIYA: SORTING DATASETS;
SYMBOLGEN: Macro variable TBL resolves to LAB
MPRINT(READTBL): PROC SORT DATA = DATA0047.LAB;
MPRINT(READTBL): BY PATID VISIT SEQNUM ;
ERROR: Variable SENUM not found.
ERROR: Variable SENUM not found.
ERROR: Variable SENUM not found.
MPRINT(READTBL): RUN;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements.
This may cause NOTE: No observations in data set.
NOTE: PROCEDURE SORT used:
real time 0.03 seconds
cpu time 0.00 seconds

Can someone help me to reslove this issue.
Thanks
Priya