Hello
I have a raw file whose name is Client_Product_Ash_HCP_Address_20171006030056_2803.txt. I read the file into the SAS but only want the part of the file name just before the second "_" from the reverse. So, basically the output file name should look like this:
Client_Product_Ash_HCP_Address
Another example would be Client_Product_Ash_HCP_Contact_20171006030058_2870.txt. The output file name should look like this:
Client_Product_Ash_HCP_Contact
Any ideas on how to use a combination of Substr and index function to achieve this goal?
Thanks
Chandan Mishra
You can use CALL SCAN to find the position of the second underscore delimited word and use P to sub-string everything from column 1 to the desired column.
27 data _null_;
28 file = 'Client_Product_Ash_HCP_Contact_20171006030058_2870.txt';
29 call scan(file,-2,p,l,'_');
30 part = substrn(file,1,p-2);
31 put _all_;
32 run;
file=Client_Product_Ash_HCP_Contact_20171006030058_2870.txt p=32 l=14 part=Client_Product_Ash_HCP_Contact _ERROR_=0 _N_=1
Use ANYDIGIT to find the location of the first digit.
Use SUBSTR to then extract the part desired.
index = anydigit(string);
string_want = substr(string, 1, index-2); *not sure how much you'll need to minus to get what you want, but you can test it and see;
You can use CALL SCAN to find the position of the second underscore delimited word and use P to sub-string everything from column 1 to the desired column.
27 data _null_;
28 file = 'Client_Product_Ash_HCP_Contact_20171006030058_2870.txt';
29 call scan(file,-2,p,l,'_');
30 part = substrn(file,1,p-2);
31 put _all_;
32 run;
file=Client_Product_Ash_HCP_Contact_20171006030058_2870.txt p=32 l=14 part=Client_Product_Ash_HCP_Contact _ERROR_=0 _N_=1
You could use SCAN function to concatenate the 2nd-last and last "words" (where '_' is the word delimiter). Then use TRANWRD to convert it to blank.
data _null_;
txt="Client_Product_Ash_HCP_Address_20171006030056_2803.txt";
drop_text='_'||catx('_',scan(txt,-2,'_'),scan(txt,-1,'_'));
want=tranwrd(txt,trim(drop_text),' ');
put (_all_) (= /);
run;
Since @data_null__ John King has already posted a wonderful solution.
I would like to post another solution.
data _null_;
file = 'Client_Product_Ash_HCP_Contact_20171006030058_2870.txt';
part = prxchange('s/[\d_]+$//',1,scan(file,1,'.'));
put _all_;
run;
Hi @Ksharp
I am trying to search for how prxchange works but it seems really complicated. The syntax is:
prxchange( regular expression|id, occurrence, source )
I understood the occurence and source part but how to define the regular expression|id part.
Thanks
Chandan Mishra
Regular expressions are actually from PERL, they don't originate with SAS, but they're highly useful so many languages now implement this.
Perl documentation and builder are here:
's/[\d_]+$//'
This means replace more than one digit or underline characters [\d_]+ (at the end of string) with null .
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.