Hi Everyone. I've a variable which has value like below. Here every part is seperated by / .
Data have; Input tpc $30.; datalines; abcde1234&9&Y / defgh0987&2&N / attire1790&4&Y Daikin1290&6&Y / rets0987&2&Y / attire1790&4&Y
Daikin1290&6&N / rets0987&2&N / attire1790&4&N
; run;
Required output; Tpc countY countN abcde1234&9&Y / defgh0987&2&N / attire1790&4&Y 13 2 Daikin1290&6&Y / rets0987&2&Y / attire1790&4&Y 12 0
Daikin1290&6&N / rets0987&2&N / attire1790&4&N 0 12
I require the count of digits preceded by & in two different fields named countY, countN.
For example: Daikin1290&6&Y / rets0987&2&Y / attire1790&4&Y in this part the digits preceded by & is 6,2,4 so the county is 12 and countN is 0 ,because there's no substring preceded by N. Likewise this approach applicable to each string value. Looking for some support. Thankyou!.
I don't get it. Why 12 and 13?
is &Y / &N always the last part of the string?
Data have;
Input tpc $50.;
datalines;
abcde1234&9&Y / defgh0987&2&N / attire1790&4&Y
Daikin1290&6&Y / rets0987&2&Y / attire1790&4&Y
Daikin1290&6&N / rets0987&2&N / attire1790&4&N
;
data want;
set have;
do i = 1 to countw(tpc, ' / ');
w = scan(tpc, i, ' / ');
l = substr(w, max(1, length(w) - 1));
d = prxchange('s/.*&(\d).*/$1/', -1, w);
if l = '&Y' then sumY = sum(sumY, input(d, 8.));
if l = '&N' then sumN = sum(sumN, input(d, 8.));
end;
keep tpc sumY sumN;
run;
Sure. Does the code work for you?
Do you want 0s ?
If you want 0s then simply do
Data have;
Input tpc $50.;
datalines;
abcde1234&9&Y / defgh0987&2&N / attire1790&4&Y
Daikin1290&6&Y / rets0987&2&Y / attire1790&4&Y
Daikin1290&6&N / rets0987&2&N / attire1790&4&N
;
data want;
set have;
sumY = 0;
sumN = 0;
do i = 1 to countw(tpc, ' / ');
w = scan(tpc, i, ' / ');
l = substr(w, max(1, length(w) - 1));
d = prxchange('s/.*&(\d).*/$1/', -1, w);
if l = '&Y' then sumY = sum(sumY, input(d, 8.));
if l = '&N' then sumN = sum(sumN, input(d, 8.));
end;
keep tpc sumY sumN;
run;
Show us the data for a row where the code does not give the right answer.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.