topic Re: Ordering or giving ranks to rows in a data set with condition. in SAS Data Management

Ordering or giving ranks to rows in a data set with condition.

mesujit — Fri, 14 Aug 2020 11:14:12 GMT

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

Re: Ordering or giving ranks to rows in a data set with condition.

Jagadishkatam — Fri, 14 Aug 2020 11:29:48 GMT

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;

Re: Ordering or giving ranks to rows in a data set with condition.

mesujit — Fri, 14 Aug 2020 13:45:21 GMT

Thanks Jag, the code helped me a lot.

Re: Ordering or giving ranks to rows in a data set with condition.

Jagadishkatam — Fri, 14 Aug 2020 14:35:57 GMT

Could you please mark it as answered so it can help others across while searching.