BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
J111
Quartz | Level 8

Hello,

Please find below data Have and  data Want.

We would like to extract X1 from the data according to the following rules:

1. it appears after the last back slash in the sentence

2. and also before the first dot following this word (if there is a dot)

3. and this word contains only capital letters or under score in it (without small letters)

4. and there is no garbage after this word (such as '..')

5. if it can not find a word that follows all theses rules than write NOWORD

 

We would like to extract X2 from the data according to the following rules:

1. it appears always after this word BPS.STQR/ or this word BPS.STQR, 

in other words it appears after BPS.STQR and is surrounded with '/' or with ',' 

 

Thanks in advance

----------------------------------------------------------------------------------------------


Data have ;
input data $60. ;
cards ;
data/dataflow/BPS.STQR/WAB/1.0/NER_PGTABC
data/dataflow/wow/BPS.STQR,WAB,1.0/NER_QZW
data/dataflow/wow/BPS.STQR,WAB,1.0/NER_QZW ..
data/dataflow/wow/BPS.STQR,WAB,1.0/NER_QZW.ABCDEFG
/availability/dataflow/*/*/*/*/-
/availability

;
Run ;

Data Want ;
input x1 $10. x2 $7. ;
cards ;
NER_PGTABC WAB
NER_QZW WAB
NER_QZW WAB
NER_QZW WAB

NOWORD

NOWORD

;
Run ;

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
data want;
    set have;
    location = find(data,'BPS.STQR');
    if location>0 then x2=scan(substr(data,location+9),1);
run;
--
Paige Miller

View solution in original post

4 REPLIES 4
J111
Quartz | Level 8

Hellow,

Seems I found a solution for calculating X1 - view data test:-

Would appreciate your help regarding X2..


Data test ;
set have ;
X1 = scan(scan(data,-1,"/"),1,'.') ;
if count(x1,lowcase(X1)) = 1 then X1 = 'NOWORD' ;
Run ;

quickbluefish
Barite | Level 11
I'm curious why you find a match for X1 in the 3rd row of your input dataset - doesn't that break the rule of 'no garbage' like '..' after the word? Or is garbage OK as long as there's a space preceding it? Also, could you define garbage? Any non A-Z or underscore? It does seem like you might get a more robust solution using one of the PRX* functions.
J111
Quartz | Level 8

A little clarification

The purpose is to clean the words from the garbage

as long as they have upercase or underscores.

 

Thanks

PaigeMiller
Diamond | Level 26
data want;
    set have;
    location = find(data,'BPS.STQR');
    if location>0 then x2=scan(substr(data,location+9),1);
run;
--
Paige Miller

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1295 views
  • 0 likes
  • 3 in conversation