BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chandan_mishra
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

View solution in original post

7 REPLIES 7
Reeza
Super User

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;

 

 

 

data_null__
Jade | Level 19

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
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User

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;
chandan_mishra
Obsidian | Level 7

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

Reeza
Super User

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:

https://perldoc.perl.org/perlre.html

https://regex101.com/

Ksharp
Super User
's/[\d_]+$//'

This means replace more than one digit or underline characters  [\d_]+ (at the end of string) with null . 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 9402 views
  • 4 likes
  • 5 in conversation