BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Satori
Quartz | Level 8

This is my code:

 

proc import datafile='/data/bvd/export.xlsx' out=b1 dbms=xlsx replace;

%macro ex(); %do i=2008 %to 2022; proc contents data=b1 out=list(keep=name) noprint; data list1; set list(keep=name); if name in ('Name','Country','CISOC','ID','Status','Legal', 'Entity','Code','Type'); keep name; data list_&i; set list; if substr(name, length(name)-3) = "&i" then output; keep name; data l_&i; set list1 list_&i; proc sql noprint; select name into :l2 separated by ' ' from l_&i; quit; data final_&i(keep=l_&i); set b1; proc sql noprint; select catx("=", name, substr(name, 1, length(name)-5)) into :rename_list separated by " " from sashelp.vcolumn where libname='HOME' and memname="FINAL_&i" and upper(trim(name)) like "%_&i"; quit; proc datasets library=work nodetails nolist; modify final_&i; rename &rename_list; run; quit; data home.final; set final_&i; %end; %mend; %ex();

Log:

NOTE: The import data set has 1421 observations and 819 variables.
NOTE: WORK.B1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           7.18 seconds
      cpu time            7.16 seconds
      

MPRINT(EX):   proc contents data=b1 out=list(keep=name) noprint;

NOTE: The data set WORK.LIST has 819 observations and 1 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

MPRINT(EX):   data list1;
MPRINT(EX):   set list(keep=name);
MPRINT(EX):   if name in ('Name','Country','CISOC','ID','Status','Legal','Entity','Code','Type');
MPRINT(EX):   keep name;

NOTE: There were 819 observations read from the data set WORK.LIST.
NOTE: The data set WORK.LIST1 has 9 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MPRINT(EX):   data list_2008;
MPRINT(EX):   set list;
MPRINT(EX):   if substr(name, length(name)-3) = "2008" then output;
MPRINT(EX):   keep name;

NOTE: There were 819 observations read from the data set WORK.LIST.
NOTE: The data set WORK.LIST_2008 has 54 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MPRINT(EX):   data l_2008;
MPRINT(EX):   set list1 list_2008;

NOTE: There were 9 observations read from the data set WORK.LIST1.
NOTE: There were 54 observations read from the data set WORK.LIST_2008.
NOTE: The data set WORK.L_2008 has 63 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MPRINT(EX):   proc sql noprint;
MPRINT(EX):   select name into :l_2008 separated by ' ' from l_2008;
MPRINT(EX):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MPRINT(EX):   data final_2008(keep=l_2008);
MPRINT(EX):   set b1;

WARNING: The variable l_2008 in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: There were 1421 observations read from the data set WORK.b1.
NOTE: The data set WORK.FINAL_2008 has 1421 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MPRINT(EX):   proc sql noprint;
WARNING: Apparent invocation of macro _2008 not resolved.
WARNING: Apparent invocation of macro _2008 not resolved.
MPRINT(EX):   select catx("=", name, substr(name, 1, length(name)-5)) into :rename_list separated by " " from sashelp.vcolumn where libname='HOME' and memname="FINAL_2008" and 
upper(trim(name)) like "%_2008";
NOTE: No rows were selected.
MPRINT(EX):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

MPRINT(EX):   proc datasets library=work nodetails nolist;
MPRINT(EX):   modify final_2008;
WARNING: Apparent symbolic reference RENAME_LIST not resolved.
NOTE: Line generated by the invoked macro "EX".
799                   rename &rename_list; run;
                             _
                             22
                             76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
MPRINT(EX):   rename &rename_list run;

ERROR 22-322: Expecting a name.  

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: Statements not processed because of errors noted above.
MPRINT(EX):   quit;

NOTE: The SAS System stopped processing this step because of errors.
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You are getting closer.  Now the issue is that no variables matched your criteria but you still tried to run the RENAME statement.   The error message you got was because since no variables met the criteria PROC SQL never created the macro variable so when you referenced it it was not there. 

 

But fixing just that will not fix the code, it will just change the error message.

For example this is the error message you get when you try to use a RENAME statement in PROC DATASETS with no actual pairs of names.

149  proc datasets lib=work nolist;
150  modify want;
151  rename ;
            -
            22
            76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
152  run;

NOTE: Statements not processed because of errors noted above.
153  quit;

So you need to prevent the PROC DATASETS step from running when no variables where found. 

 

You can do that by testing the automatic macro variable SQLOBS.

%if &sqlobs %then %do;
proc datasets library=work nodetails nolist;
  modify final_&i;
  rename &rename_list; 
run; quit;
%end;

But you do realize the rest of your macro is just gibberish also.

 

Let's look at the beginning few steps:

proc contents data=b1 out=list(keep=name) noprint;
data list1; set list(keep=name); if name in ('Name','Country','CISOC','ID','Status','Legal', 'Entity','Code','Type'); keep name;
data list_&i; set list; if substr(name, length(name)-3) = "&i" then output; keep name;
data l_&i; set list1 list_&i;

So the first thing one notices is the lack of formatting and missing RUN statements to end the steps. 

 

First  you use PROC CONTENTS to get the list of variable names from the data B1.  (Notice that B1 is not listed as an INPUT to this macro so where did it come from?)

Then you keep only the NAME variable from the output of PROC CONTENTS and only the observations that match a short list of about 9 names.  And only when the exact mixed case letters were used to define the variable's name.  So you won't find Name if it was created as NAME or name or NAme etc.

You then make another subset of the names, this time just the ones that end with _ and the value of the macro variable I.

Then you put them back together.

 

What is the goal here?  Is to somehow move the first 9 variables up in the sort order?

 

Then you make a copy of this mystery B1 dataset, but you only keep the variable named L_ followed by the value of macro variable I.   

Are you sure that such a variable exists?

 

Then comes the logic that was causing the error.  Basically you are checking this new copy of B1 that you called FINAL_&i to see if it contains any variables that end in _&i.  But since you just created it to only have L_&i you should already know the answer.

 

I suspect that the real goal is to make a subset of this mystery B1 dataset that only has 9 key variables and any other variables that have a year suffix.  With the added wrinkle of renaming them to remove the year suffix.

I am not sure why you are looping the year from 2008 to 2022, so perhaps you want to convert the data into multiple observations, one for each year?  If so then add a YEAR variable so you can distinguish the duplicated rows.

 

So try something like this:

%macro ex(dsin,dsout);
%local i keynames keep_list rename_list ;
%let keynames=Name Country CISOC ID Status Legal Entity Code Type;

%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 subset_&i;
  year = &i ;
  set b1(keep=&keynames &keep_list);
%if (&sqlobs) %then %do;
  rename &rename_list;
%end
run;

%end;

data &dsout;
  set subset_2008-subset_2022;
run;

%mend;


proc import datafile='/data/bvd/export.xlsx' out=b1 dbms=xlsx replace;
%ex(b1,home.final);

 

 

 

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

Satori, from now on, please show us the ENTIRE log for this macro (or DATA step or PROC that has errors). Do not show us just the errors, notes and warnings. We need to see all of the log.

 

I'm guessing this is the problem

 

data home.final_&i(keep=&l_&i); set b1;

 

I think you want

 

data home.final_&i(keep=l_&i); set b1;

 

--
Paige Miller
Satori
Quartz | Level 8
I added the log, after adjusting for your suggestion.
PaigeMiller
Diamond | Level 26

@Satori wrote:
I added the log, after adjusting for your suggestion.

My mistake. I left out a key piece of information. When there are errors in a macro, you need to first turn on the macro debugging tool by running this command

 

options mprint;

and then run your code and provide the entire log. Thanks!

--
Paige Miller
Satori
Quartz | Level 8
Added the log to the main message with options mprint. thanks
ballardw
Super User

You may want to consider getting into the habit of using RUN statements for any procedures or data steps inside of a macro. Because of the want the macro processor works you may run into cases where you execute macro code that is not followed by something SAS considers a trigger for the end of  a previous procedure or data step and get some pretty strange behaviors.

This can become a serious programming problem when you start using macro logic to create conditional Proc or data step statements.

 

You have several places where macro variables will not resolve in this line:

 libname='HOME' and memname='FINAL_&i' and upper(trim(name)) like '%_&i'; quit

Macro variables do not resolve inside single quotes. So the data set name of 'FINAL&i' is not legal because of the &, the like is going to not find anything related to your loop counter because of the single quotes.

 

Debug macro code by setting OPTIONS MPRINT prior to executing the macro. That will show the code actually generated and you get error messages in closer proximity to problem code. If you are using lots of complex macro logic then add MLOGIC to the options list. If using lots of complex macro variable building you may also want SYMBOLGEN to show how pieces are resolved and combined.

Tom
Super User Tom
Super User

I doubt that you actually want to find information about a dataset with a name that includes an & character.

memname='FINAL_&i' 

Macro triggers, like & and %, are not honored in strings that are bounded by single quotes.  Use double quotes.

memname="FINAL_&i"

To prevent the error message about RENAME_LIST not being defined set it to something (or really set it to nothing) before the SELECT statement. 

You might also want to get in the habit of using NLITERAL() when generating names from metadata just in case someone accidentally set VALIDVARNAME option to ANY.

%let rename_list=;
select catx("=", nliteral(name), nliteral(substr(name, 1, length(name)-5)))
  into :rename_list separated by " "
  from sashelp.vcolumn 
  where libname='HOME'
    and memname="FINAL_&i"
    and upper(trim(name)) like "%_&i"
; 
quit;

Why are you chopping off 5 characters from the name?  Shouldn't the number depend on the length of &i?

substr(name, 1, length(name)-%length(_&i))

 

Tom
Super User Tom
Super User

You are getting closer.  Now the issue is that no variables matched your criteria but you still tried to run the RENAME statement.   The error message you got was because since no variables met the criteria PROC SQL never created the macro variable so when you referenced it it was not there. 

 

But fixing just that will not fix the code, it will just change the error message.

For example this is the error message you get when you try to use a RENAME statement in PROC DATASETS with no actual pairs of names.

149  proc datasets lib=work nolist;
150  modify want;
151  rename ;
            -
            22
            76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
152  run;

NOTE: Statements not processed because of errors noted above.
153  quit;

So you need to prevent the PROC DATASETS step from running when no variables where found. 

 

You can do that by testing the automatic macro variable SQLOBS.

%if &sqlobs %then %do;
proc datasets library=work nodetails nolist;
  modify final_&i;
  rename &rename_list; 
run; quit;
%end;

But you do realize the rest of your macro is just gibberish also.

 

Let's look at the beginning few steps:

proc contents data=b1 out=list(keep=name) noprint;
data list1; set list(keep=name); if name in ('Name','Country','CISOC','ID','Status','Legal', 'Entity','Code','Type'); keep name;
data list_&i; set list; if substr(name, length(name)-3) = "&i" then output; keep name;
data l_&i; set list1 list_&i;

So the first thing one notices is the lack of formatting and missing RUN statements to end the steps. 

 

First  you use PROC CONTENTS to get the list of variable names from the data B1.  (Notice that B1 is not listed as an INPUT to this macro so where did it come from?)

Then you keep only the NAME variable from the output of PROC CONTENTS and only the observations that match a short list of about 9 names.  And only when the exact mixed case letters were used to define the variable's name.  So you won't find Name if it was created as NAME or name or NAme etc.

You then make another subset of the names, this time just the ones that end with _ and the value of the macro variable I.

Then you put them back together.

 

What is the goal here?  Is to somehow move the first 9 variables up in the sort order?

 

Then you make a copy of this mystery B1 dataset, but you only keep the variable named L_ followed by the value of macro variable I.   

Are you sure that such a variable exists?

 

Then comes the logic that was causing the error.  Basically you are checking this new copy of B1 that you called FINAL_&i to see if it contains any variables that end in _&i.  But since you just created it to only have L_&i you should already know the answer.

 

I suspect that the real goal is to make a subset of this mystery B1 dataset that only has 9 key variables and any other variables that have a year suffix.  With the added wrinkle of renaming them to remove the year suffix.

I am not sure why you are looping the year from 2008 to 2022, so perhaps you want to convert the data into multiple observations, one for each year?  If so then add a YEAR variable so you can distinguish the duplicated rows.

 

So try something like this:

%macro ex(dsin,dsout);
%local i keynames keep_list rename_list ;
%let keynames=Name Country CISOC ID Status Legal Entity Code Type;

%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 subset_&i;
  year = &i ;
  set b1(keep=&keynames &keep_list);
%if (&sqlobs) %then %do;
  rename &rename_list;
%end
run;

%end;

data &dsout;
  set subset_2008-subset_2022;
run;

%mend;


proc import datafile='/data/bvd/export.xlsx' out=b1 dbms=xlsx replace;
%ex(b1,home.final);

 

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 7 replies
  • 471 views
  • 3 likes
  • 4 in conversation