Hi i have a list of files that i import using my macro, inside this macro there is also data step that I want to use my tables names to extract months and years and add them to those tables, so i used the substr function, but i receive this error for each iteration
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 26:1 26:1 NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 26:9 NOTE: Variable Jan20 is uninitialized. NOTE: Invalid second argument to function SUBSTR at ligne 65483 colonne 223.
Here is my code :
%let rep = \\sfdrza\QQQ\adkjf\AAA\BBB\CCC\sdsdt\DDD
%let y20 = Jan20 Feb20;
/*Import*/
options msglevel=I;
options mprint;
%macro boucle_doCC(in);
%local i s;
%do i=1 %to %sysfunc(countw(&in.,%str( )));
%let s&i.=%scan(&in.,&i.,%str( ));
proc import datafile ="&rep.\&&s&i...xlsx"
out=&&s&i..
dbms=xlsx replace;
getnames=yes;
run;
data &&s&i..(keep= Brand Segment DC Trade:);
set &&s&i..;
if missing(Brand) then delete;
/*ajout des colonnes mois et année*/
year=substr(&&s&i.,-1, 2);
month=substr(&&s&i..,1, 3);
run;
%end;
%mend boucle_doCC;
By the way, when you use the `(keep= Brand Segment DC Trade:)` you are dropping year and month from your data
B
Hi,
If I'm guessing right you are using the `y20` macrovariable as an input to the macro as `in` macrovariable?
1) substr() function can not have negative value as a second parameter:
year=substr(&&s&i.,-1, 2);
2) &&s&i resolves to Jan20 and substr() expect to get text string, like "Jan20"
Maybe try something like this:
year=substr("&&s&i.", 4, 2);
month=substr("&&s&i.", 1, 3);
Bart
I did not know that substr can't go from right to left, i tried your code but does not seem to work unfortunately
Share modified code and full log. It will be easier to figure out what's wrong.
B
i get the year but not the month 😕
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 38 Le Système SAS 10:06 Thursday, September 17, 2020 29:48 WARNING: The variable 'D/C'n in the DROP, KEEP, or RENAME list has never been referenced. NOTE: Invalid numeric data, 'Feb' , at ligne 29 colonne 48. Brand=Hyundai i10 Segment=A DC=1200 Trade-in= E= Detail= Entry MSRP=10790 Monthly=115 Dpmt=1300 Month=. Mileage=50,000 Mtnce ?= Trim=1.0 67 ECO Initia Offer Launching date( Blank, i=01.02.2020 End Date (if decided)=29.02.2020 Private M/S & trend= Detail_1=Longest promo from Hyundai ( 61 months) year=20 _ERROR_=1 _N_=1
By the way, when you use the `(keep= Brand Segment DC Trade:)` you are dropping year and month from your data
B
"An expert is a person who has made all the mistakes that can be made in a very narrow field." Niels Bohr
😉
hers is "working" version on some fake test data:
/* test data */
data Jan20 Feb20 ;
input Brand Segment DC Trade;
cards;
1 2 3 4
5 6 7 8
;
run;
%let rep = \\sfdrza\QQQ\adkjf\AAA\BBB\CCC\sdsdt\DDD ;
%let y20 = Jan20 Feb20;
/*Import*/
options msglevel=I;
options mprint;
%macro boucle_doCC(in);
%local i s;
%do i=1 %to %sysfunc(countw(&in.,%str( )));
%let s&i.=%scan(&in.,&i.,%str( ));
/* commented out to avoid importing */
/*
proc import datafile ="&rep.\&&s&i...xlsx"
out=&&s&i..
dbms=xlsx replace;
getnames=yes;
run;
*/
data &&s&i..(keep= year month Brand Segment DC Trade:);
set &&s&i..;
if missing(Brand) then delete;
/*ajout des colonnes mois et année*/
year=substr("&&s&i.", 4, 2);
month=substr("&&s&i.", 1, 3);
run;
%end;
%mend boucle_doCC;
%boucle_doCC(&y20.)
B
As &&s&i, the filename. is a literal and not a variable name,
it should be enclosed by double quotes (not single quotes) to be
resolved as a literal. For example:
year = substtr("&&s&i", <start>, <length>);
was it a typo assigning start = -1 for subtracting the year ?
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.