Hi Folks,
I have following code,
%macro test (num,country);
%do i = 1 %to 3;
proc sql outobs=#
create table combined as
select distinct contractID, sourcesystemID, countryid, stage
from ifrs._09_abc
where countryid = &country. and stage = &i;
%end;
%mend;
%test(10,1);
When it ran it produces the table with only stage 3. I think the loop replaces the values. I want to have all three stages in the output table.
Thanks in advance.
Then you need to append the proc sql output to a dataset each loop, i.e.
data want; stop; run; %macro test (num,country); %do i = 1 %to 3; proc sql outobs=# create table combined as select distinct contractID, sourcesystemID, countryid, stage from ifrs._09_abc where countryid =&country. and stage=&i; quit; data want; set want combined; run; %end; %mend; %test(10,1);
So each iteration the output combined is added to want.
That being said, I don't see any point for this code at all, simply:
%let country=1;
%let stage=3; proc sort data=ifrs._09_abc out=want (keep=contractid...) nodupkey;
by contractid...;
where countryid=&country. and stage <= &stage.;
run;
Note add your other variables in where the ... is.
You always use the same name for the output table, so the second iteration overwrites the result from the first, and the third the second.
Then you need to append the proc sql output to a dataset each loop, i.e.
data want; stop; run; %macro test (num,country); %do i = 1 %to 3; proc sql outobs=# create table combined as select distinct contractID, sourcesystemID, countryid, stage from ifrs._09_abc where countryid =&country. and stage=&i; quit; data want; set want combined; run; %end; %mend; %test(10,1);
So each iteration the output combined is added to want.
That being said, I don't see any point for this code at all, simply:
%let country=1;
%let stage=3; proc sort data=ifrs._09_abc out=want (keep=contractid...) nodupkey;
by contractid...;
where countryid=&country. and stage <= &stage.;
run;
Note add your other variables in where the ... is.
Thanks RW9.
I need this code as I want 10 rows of output for each stages. So each country will have 30 rows of data.
In the first iteration you are creating a table Combined for Stage1 data.
In the next iteration you are creating a table for stage2 data with the same table name Combined which overrides the first table.
The third iteration creates a table Combined which overrides the table Combined which has Stage2 data.
So you are left with Stage3 data.
If you want to create 3 tables for 3 values you can use
create table Combined&i as ...... and append the tables
%macro test (num,country);
%do i = 1 %to 3;
proc sql outobs=#
create table combined&i as
select distinct contractID, sourcesystemID, countryid, stage
from ifrs._09_abc
where countryid = &country. and stage = &i;
quit;
proc append base= combined data = combined&i;run;
%end;
%mend;
%test(10,1);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.