BookmarkSubscribeRSS Feed
Satori
Quartz | Level 8

I'm using the following macro:

 

option mprint symbolgen;
%macro ex(dsin,dsout);
%local i keynames keep_list rename_list;
%let keynames=Cname Country ISO ID Status Form Type Code Filing
	NACE NAICS SIC;
%do i=2008 %to 2022;
proc contents data=&dsin out=list(keep=name) noprint; run;
proc sql noprint; select nliteral(name), catx('=', nliteral(name), substr(name, 1, length(name)-5)) into
	:keep_list separated by ' ', :rename_list separated by ' ' from list where upcase(trim(name)) like "%_&i"; quit;
data b_&i; set &dsin(keep=&keynames &keep_list); %if (&sqlobs) %then %do; rename &rename_list; %end run;
%end;
data &dsout; set b_2008-b_2022; run;
%mend;

proc import datafile="/data/Export_us.xlsx" out=us dbms=xlsx replace;
%ex(us,o_us);

proc import datafile="/data/Export_dc.xlsx" out=dc dbms=xlsx replace;
%ex(dc,o_dc);

option nomprint nosymbolgen;
*end of macro;

My problem is that both o_us and o_dc (the output files) are the same. Any idea why this is happening?

2 REPLIES 2
Quentin
Super User

Can you show the log from running this code?

 

If I had to guess, this clause:

where upcase(trim(name)) like "%_&i"

Might be returning 0 obs, which would mean the macro variable RENAMELIST will be empty.

 

But if you show the log, from running the code with MPRINT and SYMBOLGEN turned on, it should be easier to tell what's happening.

 

Are you just transposing this data from wide (with lots of variables like x_2001 y_2001 x_2002 y_2002 to long?  If so, you might want to look into use PROC TRANPOSE, or transposing it using a singe DATA step with arrays.

 

 

BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super User

What do you get when you turn on MPRINT?  Not sure that SYMBOLGEN is going to help with debugging in this case, it will just clutter the SAS LOG.

 

It might help to clean up the formatting (perhaps posting into the forum has removed some of the white space?).

That made it easier to notice that

 

1) The the PROC CONTENTS should be before the %DO loop.

2) The second %END is missing the ending semicolon.

 

Are you trying to check for literal _ character in the end of the variable names? If so you need to use and escape character.

 

Does this help?

 

%macro ex(dsin,dsout);
%local i keynames keep_list rename_list;
%let keynames=Cname Country ISO ID Status Form Type Code Filing
	NACE NAICS SIC
;
proc contents data=&dsin out=list(keep=name) noprint; run;
%do i=2008 %to 2022;
proc sql noprint;
select nliteral(name)
     , catx('=', nliteral(name), substr(name, 1, length(name)-5))
  into :keep_list separated by ' '
     , :rename_list separated by ' ' 
  from list 
  where upcase(trim(name)) like "%^_&i" escape '^'
; 
quit;
data b_&i;
  set &dsin(keep=&keynames &keep_list);
  %if (&sqlobs) %then %do;
  rename &rename_list; 
  %end;
run;
%end;
data &dsout; 
  set b_2008-b_2022; 
run;
%mend;

 

sas-innovate-2024.png

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.

 

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
  • 2 replies
  • 289 views
  • 0 likes
  • 3 in conversation