substract strings from dataset name and put it in a variable

[skavli]

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;

[yabwon]

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

[skavli]

I did not know that substr can't go from right to left, i tried your code but does not seem to work unfortunately

[yabwon]

Share modified code and full log. It will be easier to figure out what's wrong.

B

[skavli]

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

[yabwon · Accepted solution]

By the way, when you use the `(keep= Brand Segment DC Trade:)` you are dropping year and month from your data 

B

[skavli]

OMG THAT WAS IT I'M SO STUPID THAAAANKS

[yabwon]

"An expert is a person who has made all the mistakes that can be made in a very narrow field." Niels Bohr 

😉

[yabwon]

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

[Shmuel]

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 ?

[skavli]

No i thought that substr could go from right to left