I want to give the Order/ranking to my data set TAXDEPT (output in Rank col.) as shown below using PROC SQL.
My source data set is in below form now:
SiteID is already in sorted form.
where ever SiteTax == allTax, i have to give ranks like 1,2,3,...
where ever SiteTax != allTax, i have to give sub-ranks like 3.1, 3.2, ....4.1, 4.2, ....
like for SiteID 211 below i need like; 4.1, 4.2, 4.3
TAXDEPT:
Country | SiteID | SiteTax | Rank |
All Country | allSite | allTax | 1 |
Austria | allSite | allTax | 2 |
Austria | 32 | allTax | 3 |
Austria | 32 | a1 | 3.1 |
Austria | 211 | allTax | 4 |
Austria | 211 | a2 | 4.1 |
Austria | 211 | a3 | 4.2 |
Austria | 211 | a9 | 4.3 |
Austria | 330 | allTax | 5 |
Austria | 330 | b2 | 5.1 |
Austria | 330 | b3 | 5.2 |
Austria | 330 | b1 | 5.3 |
Austria | 330 | c2 | 5.4 |
Canada | allSite | allTax | 6 |
Canada | 211 | allTax | 7 |
Canada | 211 | q1 | 7.1 |
Canada | 211 | q3 | 7.2 |
Canada | 211 | q2 | 7.3 |
Canada | 579 | allTax | 8 |
Canada | 579 | 8.1 | |
Canada | 579 | 8.2 | |
Canada | 579 | 8.3 | |
Canada | 666 | allTax | 9 |
Canada | 666 | 9.1 |
please try the below code
data have;
input Country$ SiteID$ SiteTax$ ;
cards;
AllCountry allSite allTax
Austria allSite allTax
Austria 32 allTax
Austria 32 a1
Austria 211 allTax
Austria 211 a2
Austria 211 a3
Austria 211 a9
Austria 330 allTax
Austria 330 b2
Austria 330 b3
Austria 330 b1
Austria 330 c2
Canada allSite allTax
Canada 211 allTax
Canada 211 q1
Canada 211 q3
Canada 211 q2
Canada 579 allTax
Canada 579 .
Canada 579 .
Canada 579 .
Canada 666 allTax
Canada 666 .
;
data want;
set have;
by country siteid notsorted;
retain rank1 rank2;
if SiteTax eq 'allTax' then do;
if first.siteid then rank1+1;
if first.siteid then rank2=.;
end;
if SiteTax ne 'allTax' then rank2+0.1;
rank=sum(rank1,rank2);
drop rank1 rank2;
run;
Thanks Jag, the code helped me a lot.
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.