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 .
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.