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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.