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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.