Hello all,
I am attempting to output in a data set text like this for auto caluclation in an excell file that SAS ODS produces for me:
=EXACT({table_name1}!A1,{table_name2}!A1)
=EXACT({table_name1}!A2,{table_name2}!A2)
=EXACT({table_name1}!A3,{table_name2}!A3)
=EXACT({table_name1}!A#...,{table_name2}!A#...)
My code:
%macro create(howmany);
%do i=1 %to &howmany;
data check_db;
a&i.="=EXACT(census!A&i.,cendev!A&i.)";
B&i.="=EXACT(census!B&i.,cendev!B&i.)";
c&i.="=EXACT(census!C&i.,cendev!C&i.)";
output;
run;
%end;
%mend create;
%create(1);
proc print data=check_db;
run;
My output:
Obs a1 B1 c1
1 =EXACT(census!A1,cendev!A1) =EXACT(census!B1,cendev!B1) =EXACT(census!C1,cendev!C1)
However when I put in 2, or 3, 4, ...
I still only get one line (1 obs) and the numbers all change to the number passed in. Can someone straighten me out here?
What I want is if I pass in 20 I get 20 obs all numbered from 1 to 20.
%create(2);
proc print data=check_db;
run;
14:52 Wednesday, September 21, 2016 837
Obs a2 B2 c2
1 =EXACT(census!A2,cendev!A2) =EXACT(census!B2,cendev!B2) =EXACT(census!C2,cendev!C2)
PS I get no errors:
4097 %macro create(howmany);
4098 %do i=1 %to &howmany;
4099 data check_db;
4100 a&i.="=EXACT(census!A&i.,cendev!A&i.)";
4101 /*output;*/
4102 B&i.="=EXACT(census!B&i.,cendev!B&i.)";
4103 /*output;*/
4104 c&i.="=EXACT(census!C&i.,cendev!C&i.)"; output;
4105
4106 run;
4107 %end;
4108 %mend create;
4109
4110 %create(2);
NOTE: The data set WORK.CHECK_DB has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: The data set WORK.CHECK_DB has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
4111
4112 proc print data=check_db;
4113 run;
NOTE: There were 1 observations read from the data set WORK.CHECK_DB.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
TIA. -KJ
Each iteration through the %DO is creating a complete DATA step. Even your log shows that two data steps completed.
If you want to create one DATA step, with additional assignment statements, you have to move two pieces:
- the DATA statement goes before the %DO loop
- the RUN statement goes after the %END statement
If you were to add this statement before running the macro, you could more easily follow what is happening:
options mprint;
Each iteration through the %DO is creating a complete DATA step. Even your log shows that two data steps completed.
If you want to create one DATA step, with additional assignment statements, you have to move two pieces:
- the DATA statement goes before the %DO loop
- the RUN statement goes after the %END statement
If you were to add this statement before running the macro, you could more easily follow what is happening:
options mprint;
%macro create(howmany);
data check_db;
%do i=1 %to &howmany;
A="=EXACT(census!A&i.,cendev!A&i.)";
B="=EXACT(census!B&i.,cendev!B&i.)";
C="=EXACT(census!C&i.,cendev!C&i.)";
output;
%end;
run;
%mend create;
%create(4500);
title 'Auto check matching counts';
proc print data=check_db noobs n;
run;
title;
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.