BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Marzi
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

10 REPLIES 10
Reeza
Super User

If your code is as shown, you declare your freqbynum macro AFTER you call it. It needs to exist before. 

 

Order of operation. 

Marzi
Obsidian | Level 7
the problem is not from the order of MACRO, I fix it, but still getting the same answer.
Reeza
Super User

Why not query the sashelp.vcolumn data instead? 

 

I dont see anything in your code. Post the full log using MPRINT and SYMBOLGEN

 

 

Marzi
Obsidian | Level 7
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

Ksharp
Super User

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;
Ksharp
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
Thanks, @RW9 I need to call variables by their position, rather than their name
Reeza
Super User

@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

 

 

Marzi
Obsidian | Level 7

Thank @Reeza, it was helpful.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2758 views
  • 4 likes
  • 4 in conversation