How to expected values from a character string

[knveraraju91]

Dear,

I have a variable(clist) has values.

CLIST

1. ATC|N|NERVOUS SYSTEM; ATC|N06|PSYCHOANALEPTICS; ATC|N06B|PSYCHOSTIMULANTS, AGENTS USED FOR ADHD AND NOOTROPICS; ATC|N06BA|CENTRALLY ACTING SYMPATHOMIMETICS; PRODUCT|062107 01 001|ARMODAFINIL; PRODUCTSYNONYM|062107 01 002|NUVIGIL;

2.ATC|R|RESPIRATORY SYSTEM; ATC|R05|COUGH AND COLD PREPARATIONS; ATC|R05X|OTHER COLD PREPARATIONS; PRODUCT|000558 01 001|VICK VAPOUR-RUB; PRODUCTSYNONYM|000558 01 002|VICKS VAPORUB /00055801/;

I need to extract the ATC values(ATC values start with ATC) from it into 4 variables;

By using the following code I am getting the expecting output for first observation. But for second observation, eventhough 4th ATC value is missing but values are populated for CMATC4 and CMATC4C. The values for CMATC4 and CMATC4C should be blank as there is no 4th ATC. Please help

Code used:
CMATC1 = scan(clist,3,'|;/');
CMATC1C = scan(clist,2,'|;/');
CMATC2 = scan(clist,6,'|;/');
CMATC2C = scan(clist,5,'|;/');
CMATC3 = scan(clist,9,'|;/');
CMATC3C = scan(clist,8,'|;/');
CMATC4 = scan(clist,12,'|;/');
CMATC4C = scan(clist,11,'|;/');

expecting for the output:

for first observation:

CMATC1C= 'NERVOUS SYSTEM';
CMATC1='N';

CMATC2C='PSYCHOANALEPTICS';
CMATC2='N06';

CMATC3C='PSYCHOSTIMULANTS, AGENTS USED FOR ADHD AND NOOTROPICS';
CMATC3='N06B';

CMATC4C='CENTRALLY ACTING SYMPATHOMIMETICS';
CMATC4='N06BA';

for second observation:

CMATC1C= 'RESPIRATORY SYSTEM';
CMATC1='R';

CMATC2C='COUGH AND COLD PREPARATIONS';
CMATC2='R05';

CMATC3C='OTHER COLD PREPARATIONS';
CMATC3='R05x';

CMATC4C='';
CMATC4='';

[JohnHoughton]

Split first using the semi-colon as the delimiter.

Then check if the split element starts with 'ATC' and if it does take the 2nd and 3rd element.

data have;

CLIST="ATC|N|NERVOUS SYSTEM; ATC|N06|PSYCHOANALEPTICS; ATC|N06B|PSYCHOSTIMULANTS, AGENTS USED FOR ADHD AND NOOTROPICS; ATC|N06BA|CENTRALLY ACTING SYMPATHOMIMETICS; PRODUCT|062107 01 001|ARMODAFINIL; PRODUCTSYNONYM|062107 01 002|NUVIGIL;";
output; 
clist="ATC|R|RESPIRATORY SYSTEM; ATC|R05|COUGH AND COLD PREPARATIONS; ATC|R05X|OTHER COLD PREPARATIONS; PRODUCT|000558 01 001|VICK VAPOUR-RUB; PRODUCTSYNONYM|000558 01 002|VICKS VAPORUB /00055801/;";
output; 
run;

data want (drop=clist clistpart i);
set have;
array CMATC{4} $50  CMATC1-CMATC4;
array CMATCXC{4} $8 CMATC1C CMATC2C CMATC3C CMATC4C ;
do i=1 to dim(CMATC);
clistpart=strip(scan(clist,i,';'));
if clistpart=:"ATC"  then do;
CMATC[i] = scan(clistpart,3,'|;/');
CMATCXC[i] = scan(clistpart,2,'|;/');
end;
end;
run;

[ballardw]

You have now moved into the realm of PARSING a line of data. If I recall from a previous thread, YOU created the CLIST. It is likely time to go back and consider what you are actually doing with CLIST and if you should have placed it all one line.

This would be much easier to manage (probably for many purposes) if the data looked more like:

Keyvalue1|ATC|N|NERVOUS SYSTEM; 
Keyvalue1|ATC|N06|PSYCHOANALEPTICS; 
Keyvalue1|ATC|N06B|PSYCHOSTIMULANTS, AGENTS USED FOR ADHD AND NOOTROPICS; 
Keyvalue1|ATC|N06BA|CENTRALLY ACTING SYMPATHOMIMETICS; 
Keyvalue1|PRODUCT|062107 01 001|ARMODAFINIL; 
Keyvalue1|PRODUCTSYNONYM|062107 01 002|NUVIGIL;
Keyvalue2|ATC|R|RESPIRATORY SYSTEM; 
Keyvalue2|ATC|R05|COUGH AND COLD PREPARATIONS; 
Keyvalue2|ATC|R05X|OTHER COLD PREPARATIONS; 
Keyvalue2|PRODUCT|000558 01 001|VICK VAPOUR-RUB; 
Keyvalue2|PRODUCTSYNONYM|000558 01 002|VICKS VAPORUB /00055801/;

And you had variables to indicate the Keyvalue, role (ATC, Product, ProductSynonym) and similar (or possibly have the Product and Productsynonym on the first ATC line only and RETAIN the information).

Do you have anything that might have only 2 ATC? How about 5? Having the data in that "wide" format means extra parsing in may places for other coding but if you have one observation then the code is likely to much simpler in the long run.

[Ksharp]

You didn't post the output you want yet .

data have;
CLIST="ATC|N|NERVOUS SYSTEM; ATC|N06|PSYCHOANALEPTICS; ATC|N06B|PSYCHOSTIMULANTS, AGENTS USED FOR ADHD AND NOOTROPICS; ATC|N06BA|CENTRALLY ACTING SYMPATHOMIMETICS; PRODUCT|062107 01 001|ARMODAFINIL; PRODUCTSYNONYM|062107 01 002|NUVIGIL;";
output; 
clist="ATC|R|RESPIRATORY SYSTEM; ATC|R05|COUGH AND COLD PREPARATIONS; ATC|R05X|OTHER COLD PREPARATIONS; PRODUCT|000558 01 001|VICK VAPOUR-RUB; PRODUCTSYNONYM|000558 01 002|VICKS VAPORUB /00055801/;";
output; 
run;
data temp;
 set have;
 n+1;
 pid=prxparse('/ATC[\|;\/][^\|;\/]+[\|;\/][^\|;\/]+/io');
 start=1;
 stop=length(clist);
 call prxnext(pid,start,stop,clist,p,l);
 do while(p>0);
  temp=substr(clist,p,l);
  CMATC = scan(temp,2,'|');
  CMATCC = scan(temp,3,'|');
  output;
  call prxnext(pid,start,stop,clist,p,l);
 end;
keep n clist CMATC CMATCC ;
run;
proc sql noprint;
select max(n) into : n 
 from (select count(*) as n from temp group by n);
quit;
proc summary data=temp;
by n clist;
output out=want idgroup(out[&n] (CMATC CMATCC)=);
run;