BookmarkSubscribeRSS Feed
Bhargav_Movva
Fluorite | Level 6

My data looks like this..

LIMCEE TABLET
CELINTABLET
AMOXYCLAV CAP
RECLIMET OD TAB
GLYCOMET SR TAB
REFRESH TEARS
WELL WOMEN CAPSULE
RENERVECAPSULE
MET XL TABLET
MET XL AM TABLET
SHELCAL
SHELCAL HD
SHELCAL HD12
TENCLIMET TABLET
ZITAMET PLUS TABLET
PREGAB M CAP
LOSAR H40 TABLET
LOSAR H80 TAB
PRAZOPRESS XL 25 TABLET

 

I want to create separate dataset for names ending with TAB and TABLET as TAB_INFO  and

names ending with CAP and CAPSULES as CAP_INFO.Tried something with substr and substrn..but nothing worked

 

8 REPLIES 8
Bhargav_Movva
Fluorite | Level 6

My data looks like this..

LIMCEE TABLET
CELINTABLET
AMOXYCLAV CAP
RECLIMET OD TAB
GLYCOMET SR TAB
REFRESH TEARS
WELL WOMEN CAPSULE
RENERVECAPSULE
MET XL TABLET
MET XL AM TABLET
SHELCAL
SHELCAL HD
SHELCAL HD12
TENCLIMET TABLET
ZITAMET PLUS TABLET
PREGAB M CAP
LOSAR H40 TABLET
LOSAR H80 TAB
PRAZOPRESS XL 25 TABLET

 

I want to create separate dataset for names ending with TAB and TABLET as TAB_INFO  and

names ending with CAP and CAPSULES as CAP_INFO.Tried something with substr and substrn..but nothing worked

 

Miracle
Barite | Level 11

maybe something like this? Character Functions

data have;
input string $35.;
datalines;
LIMCEE TABLET
CELINTABLET
AMOXYCLAV CAP
RECLIMET OD TAB
GLYCOMET SR TAB
REFRESH TEARS
WELL WOMEN CAPSULE
RENERVECAPSULE
MET XL TABLET
MET XL AM TABLET
SHELCAL
SHELCAL HD
SHELCAL HD12
TENCLIMET TABLET
ZITAMET PLUS TABLET
PREGAB M CAP
LOSAR H40 TABLET
LOSAR H80 TAB
PRAZOPRESS XL 25 TABLET
;
run;

data TAB_INFO CAP_INFO OTHERS; 
	set have;
	if substr(upcase(string),length(string)-5)='TABLET' or substr(upcase(string),length(string)-2)='TAB' then output TAB_INFO;
		else if substr(upcase(string),length(string)-6)='CAPSULE' or substr(upcase(string),length(string)-2)='CAP' then output CAP_INFO;
			else output OTHERS;
run; 

 

Bhargav_Movva
Fluorite | Level 6

Hey thanks !!!! Wong it worked.But can you try with substrr(right of) function??

art297
Opal | Level 21

That does use substr (right of) function. In this case the = is merely a substitute for eq and performs the right of action.

 

Art, CEO, AnalystFinder.com

 

Tom
Super User Tom
Super User

Looks like something that it easy to handle with SCAN() function. You can use negative index to scan from the right instead of left.

data TAB_INFO CAP_INFO;
  set have ;
  if scan(MYVAR,-1) in ('TAB','TABLET') then output TAB_INFO;
  else if scan(MYVAR,-1) in ('CAP','CAPSULES') then output CAP_INFO;
run;

Or perhaps even better you can use the SELECT statement.

data TAB_INFO CAP_INFO;
  set have ;
  select (scan(MYVAR,-1));
    when ('TAB','TABLET') output TAB_INFO;
    when ('CAP','CAPSULES') output CAP_INFO;
    otherwise;
  end;
run;

 

Bhargav_Movva
Fluorite | Level 6

I think scan function is not correct in this case,if  you see the data,in some records CAP/CAPSULE/TAB/TABLET are a not separately mentioned.I mean tab and cap are not separated as words.As per my knpwledge is concerned scan works on word basis.Hence here substr is perfect

Tom
Super User Tom
Super User

@Bhargav_Movva wrote:

I think scan function is not correct in this case,if  you see the data,in some records CAP/CAPSULE/TAB/TABLET are a not separately mentioned.I mean tab and cap are not separated as words.As per my knpwledge is concerned scan works on word basis.Hence here substr is perfect


I depends on what you are trying to do. Your example string will scan into words using the default delimiters. You can use other delimiters with the scan() function.

205  data _null_;
206    str='CAP/CAPSULE/TAB/TABLET';
207    lastword=scan(str,-1);
208    put (str lastword) (=);
209  run;

str=CAP/CAPSULE/TAB/TABLET lastword=TABLET
TarunKumar
Pyrite | Level 9

data have;
input product_name$40.;
datalines;
LIMCEE TABLET
CELINTABLET
AMOXYCLAV CAP
RECLIMET OD TAB
GLYCOMET SR TAB
REFRESH TEARS
WELL WOMEN CAPSULE
RENERVECAPSULE
MET XL TABLET
MET XL AM TABLET
SHELCAL
SHELCAL HD
SHELCAL HD12
TENCLIMET TABLET
ZITAMET PLUS TABLET
PREGAB M CAP
LOSAR H40 TABLET
LOSAR H80 TAB
PRAZOPRESS XL 25 TABLET
;
run;

data want;
set have;
if index(product_name,"TABLET") then Remark = 'INFO_TAB';
else if index(product_name,"TAB") then Remark = 'INFO_TAB';
else if index(product_name,"CAP") then Remark = 'INFO_CAP';
else if index(product_name,"CAPSULE") then Remark = 'INFO_CAP';


RUN;

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
  • 8 replies
  • 1136 views
  • 0 likes
  • 5 in conversation