I want to create a loop for calling variable names, such at i-th loop, the name of the first i column will display, however, my code is only working for the first loop. this is my code:
DATA TEMP;
INPUT ID $ NAME $ SALARY DEPARTMENT $;
DATALINES;
1 Rick 623.3 IT
2 Dan 515.2 Operations
3 Michelle 611 IT
4 Ryan 729 HR
5 Gary 843.25 Finance
6 Nina 578 IT
7 Simon 632.8 Operations
8 Guru 722.5 Finance
;
RUN;
%macro freqbynum(data=,num=);
%local varlist;
proc transpose data=&data(obs=0) out=varnames;
var _all_;
run;
PROC SQL;
CREATE TABLE &data AS
SELECT * FROM varnames(obs=&num);
QUIT;
run;
PROC PRINT data = &data;
RUN;
%mend;
%MACRO loop;
%DO i = 1 %to 4;
%freqbynum(data= TEMP ,num= &i);
%END;
%MEND loop;
%loop;
I'd appreciate it if someone tells me what is my mistake, and how I can figure it out?
You overwrite the original table. That is not right.
DATA TEMP;
INPUT ID $ NAME $ SALARY DEPARTMENT $;
DATALINES;
1 Rick 623.3 IT
2 Dan 515.2 Operations
3 Michelle 611 IT
4 Ryan 729 HR
5 Gary 843.25 Finance
6 Nina 578 IT
7 Simon 632.8 Operations
8 Guru 722.5 Finance
;
RUN;
%macro freqbynum(data=,num=);
%local varlist;
proc transpose data=&data(obs=0) out=_varnames;
var _all_;
run;
PROC SQL;
CREATE TABLE _&data AS
SELECT * FROM _varnames(obs=&num);
QUIT;
run;
PROC PRINT data = _&data;
RUN;
%mend;
%MACRO loop;
%DO i = 1 %to 4;
%freqbynum(data= TEMP ,num= &i);
%END;
%MEND loop;
%loop;
If your code is as shown, you declare your freqbynum macro AFTER you call it. It needs to exist before.
Order of operation.
Why not query the sashelp.vcolumn data instead?
I dont see anything in your code. Post the full log using MPRINT and SYMBOLGEN
1365 DATALINES;
NOTE: The data set WORK.TEMP has 8 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
1374 ;
1375 RUN;
1376
1377 %macro freqbynum(data=,num=);
1378 %local varlist;
1379 proc transpose data=&data(obs=0) out=varnames;
1380 var _all_;
1381 run;
1382 PROC SQL;
1383 CREATE TABLE &data AS
1384 SELECT * FROM varnames(obs=&num);
1385 QUIT;
1386 run;
1387 PROC PRINT data = &data;
1388 RUN;
1389 %mend;
1390 %MACRO loop;
1391 %DO i = 1 %to 4;
1392 %freqbynum(data= TEMP ,num= &i);
1393 %END;
1394 %MEND loop;
1395
1396 options mprint mlogic SYMBOLGEN;
1397 %loop;
MLOGIC(LOOP): Beginning execution.
MLOGIC(LOOP): %DO loop beginning; index variable I; start value is 1; stop value is 4; by value is 1.
MLOGIC(FREQBYNUM): Beginning execution.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(FREQBYNUM): Parameter DATA has value TEMP
MLOGIC(FREQBYNUM): Parameter NUM has value 1
MLOGIC(FREQBYNUM): %LOCAL VARLIST
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM): var _all_;
MPRINT(FREQBYNUM): run;
NOTE: Numeric variables in the input data set will be converted to character in the output data set.
NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 4 observations and 1 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(FREQBYNUM): PROC SQL;
SYMBOLGEN: Macro variable DATA resolves to TEMP
SYMBOLGEN: Macro variable NUM resolves to 1
MPRINT(FREQBYNUM): CREATE TABLE TEMP AS SELECT * FROM varnames(obs=1);
NOTE: Table WORK.TEMP created, with 1 rows and 1 columns.
MPRINT(FREQBYNUM): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(FREQBYNUM): run;
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): PROC PRINT data = TEMP;
MPRINT(FREQBYNUM): RUN;
NOTE: There were 1 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.05 seconds
cpu time 0.01 seconds
MLOGIC(FREQBYNUM): Ending execution.
MPRINT(LOOP): ;
MLOGIC(LOOP): %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(FREQBYNUM): Beginning execution.
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(FREQBYNUM): Parameter DATA has value TEMP
MLOGIC(FREQBYNUM): Parameter NUM has value 2
MLOGIC(FREQBYNUM): %LOCAL VARLIST
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM): var _all_;
MPRINT(FREQBYNUM): run;
NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 1 observations and 2 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(FREQBYNUM): PROC SQL;
SYMBOLGEN: Macro variable DATA resolves to TEMP
SYMBOLGEN: Macro variable NUM resolves to 2
MPRINT(FREQBYNUM): CREATE TABLE TEMP AS SELECT * FROM varnames(obs=2);
NOTE: Table WORK.TEMP created, with 1 rows and 2 columns.
MPRINT(FREQBYNUM): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
MPRINT(FREQBYNUM): run;
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): PROC PRINT data = TEMP;
MPRINT(FREQBYNUM): RUN;
NOTE: There were 1 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.05 seconds
cpu time 0.00 seconds
MLOGIC(FREQBYNUM): Ending execution.
MPRINT(LOOP): ;
MLOGIC(LOOP): %DO loop index variable I is now 3; loop will iterate again.
MLOGIC(FREQBYNUM): Beginning execution.
SYMBOLGEN: Macro variable I resolves to 3
MLOGIC(FREQBYNUM): Parameter DATA has value TEMP
MLOGIC(FREQBYNUM): Parameter NUM has value 3
MLOGIC(FREQBYNUM): %LOCAL VARLIST
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM): var _all_;
MPRINT(FREQBYNUM): run;
NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 2 observations and 2 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(FREQBYNUM): PROC SQL;
SYMBOLGEN: Macro variable DATA resolves to TEMP
SYMBOLGEN: Macro variable NUM resolves to 3
MPRINT(FREQBYNUM): CREATE TABLE TEMP AS SELECT * FROM varnames(obs=3);
NOTE: Table WORK.TEMP created, with 2 rows and 2 columns.
MPRINT(FREQBYNUM): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.03 seconds
MPRINT(FREQBYNUM): run;
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): PROC PRINT data = TEMP;
MPRINT(FREQBYNUM): RUN;
NOTE: There were 2 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.05 seconds
cpu time 0.00 seconds
MLOGIC(FREQBYNUM): Ending execution.
MPRINT(LOOP): ;
MLOGIC(LOOP): %DO loop index variable I is now 4; loop will iterate again.
MLOGIC(FREQBYNUM): Beginning execution.
SYMBOLGEN: Macro variable I resolves to 4
MLOGIC(FREQBYNUM): Parameter DATA has value TEMP
MLOGIC(FREQBYNUM): Parameter NUM has value 4
MLOGIC(FREQBYNUM): %LOCAL VARLIST
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM): var _all_;
MPRINT(FREQBYNUM): run;
NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 2 observations and 2 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(FREQBYNUM): PROC SQL;
SYMBOLGEN: Macro variable DATA resolves to TEMP
SYMBOLGEN: Macro variable NUM resolves to 4
MPRINT(FREQBYNUM): CREATE TABLE TEMP AS SELECT * FROM varnames(obs=4);
NOTE: Table WORK.TEMP created, with 2 rows and 2 columns.
MPRINT(FREQBYNUM): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MPRINT(FREQBYNUM): run;
SYMBOLGEN: Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM): PROC PRINT data = TEMP;
MPRINT(FREQBYNUM): RUN;
NOTE: There were 2 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds
MLOGIC(FREQBYNUM): Ending execution.
MPRINT(LOOP): ;
MLOGIC(LOOP): %DO loop index variable I is now 5; loop will not iterate again.
MLOGIC(LOOP): Ending execution.
The Full Log
You overwrite the original table. That is not right.
DATA TEMP;
INPUT ID $ NAME $ SALARY DEPARTMENT $;
DATALINES;
1 Rick 623.3 IT
2 Dan 515.2 Operations
3 Michelle 611 IT
4 Ryan 729 HR
5 Gary 843.25 Finance
6 Nina 578 IT
7 Simon 632.8 Operations
8 Guru 722.5 Finance
;
RUN;
%macro freqbynum(data=,num=);
%local varlist;
proc transpose data=&data(obs=0) out=_varnames;
var _all_;
run;
PROC SQL;
CREATE TABLE _&data AS
SELECT * FROM _varnames(obs=&num);
QUIT;
run;
PROC PRINT data = _&data;
RUN;
%mend;
%MACRO loop;
%DO i = 1 %to 4;
%freqbynum(data= TEMP ,num= &i);
%END;
%MEND loop;
%loop;
Or Simple as IML.
DATA TEMP;
INPUT ID $ NAME $ SALARY DEPARTMENT $;
DATALINES;
1 Rick 623.3 IT
2 Dan 515.2 Operations
3 Michelle 611 IT
4 Ryan 729 HR
5 Gary 843.25 Finance
6 Nina 578 IT
7 Simon 632.8 Operations
8 Guru 722.5 Finance
;
RUN;
proc iml;
x=contents(temp);
do i=1 to nrow(x);
print (x[1:i]);
end;
quit;
I totally agree with @Reeza here, your code doesn't really do any that this wouldn't do in a far more succient way:
data want; set sashelp.vcolumn (where=(libname="WORK" and memname="TEMP") keep=name); run;
@Marzi Please look at the SASHELP.VCOLUMN website. There's a variable that contains the order of the variables, I can't recall the name now. Add a clause to the query
and order < &var_nums
Thank @Reeza, it was helpful.
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.