Proc import in macro function : OUT argument, remove last character

[FredLag]

Hi Sas community,

I am new to the sas software so forgive me if I make rookie mistakes. I've got multiple CSV file that I want to import in SAS with a macro-function.

All those files names end with "_2018" that I want to remove when importing datafile.

Some files names have only one underscore, and some have two. Example :

games_2018

box_scores_2018

Here's what I got so far :

%macro import(devoir1);

FILENAME REFFILE "C:\mypath\&devoir1..csv";

	PROC IMPORT
		DATAFILE=REFFILE 
		DBMS=CSV
		OUT= %sysfunc(compress(&devoir1. ,"_2018")) REPLACE; 
		GETNAMES=YES;
	RUN;

%mend import;

%import (box_scores_2018);

So this code works, but remove all "_" from the name, so that "box_score_2018" is import as "boxscore". I would like to keep the first on to get "box_score".

If someone has an idea on how to do this, it would be very appreciated!

Thanks a lot!

[Tom · Accepted solution]

Do you always want to remove the last 5 characters?

%let dsn=%substr(&devoir1,1,%length(&devoir1)-5);
...
out=&dsn

Or do you want to remove anything after the last underscore?

%let dsn=&devoir1;
%if %index(&dsn,_)>1 %then 
  %let dsn=%substr(&dsn,1,%length(&dsn)-%length(%scan(&dsn,-1,_))-1)
;

[FredLag]

The first option! Thanks a lot!!

[PaigeMiller]

You probably could use the FIND function to find the string '_2018', and then delete everything before that.

%macro import(devoir1);
data _null_;
    z=find("&devoir1",'_2018');
    new_name=substr("&devoir1",1,z-1);
    call symputx('new_name',new_name);
run;

FILENAME REFFILE "C:\mypath\&devoir1..csv";

	PROC IMPORT
		DATAFILE=REFFILE 
		DBMS=CSV
		OUT=&new_name REPLACE; 
		GETNAMES=YES;
	RUN;

%mend import;

%import (box_scores_2018)