Assign values to Macro from SQL Database

Reply
N/A
Posts: 0

Assign values to Macro from SQL Database

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.
N/A
Posts: 0

Re: Assign values to Macro from SQL Database

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
N/A
Posts: 0

Re: Assign values to Macro from SQL Database

Thanks that worked. It was really helpful.
N/A
Posts: 0

Re: Assign values to Macro from SQL Database

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
Ask a Question
Discussion stats
  • 3 replies
  • 400 views
  • 0 likes
  • 1 in conversation