Giving a number by unique string

[Kiteulf]

I am having something like in sql;

Campaignname is a string, APPLICATIONCAMPAIGNS_SRGT_ID is a numeric

SELECT DISTINCT [CampaignName]
,COUNT(cAMPAIGNNAME) AS NBROFCAMPAIGNS
,ROW_NUMBER() OVER(Order by min(APPLICATIONCAMPAIGNS_SRGT_ID)) AS CampaignNumber
FROM  Applicationcampaigns
GROUP BY CampaignName
ORDER BY CampaignNumber asc

This gives me a number for each unique observation of campaignname. It is giving out numbers by when it was first observed in APPLICATIONCAMPAIGNS_SRGT_ID

How do I do the same in SAS?

[Kurt_Bremser · Accepted solution]

Using PROC SUMMARY and a DATA step:

data Applicationcampaigns;
input Campaignname $ APPLICATIONCAMPAIGNS_SRGT_ID;
datalines;
A 2
A 4
B 7
C 1
C 5
C 6
;


proc summary data=Applicationcampaigns nway;
class campaignname;
var applicationcampaigns_srgt_id;
output out=sum (drop=_type_ rename=(_freq_=NBROFCAMPAIGNS)) min()=;
run;

proc sort data=sum;
by applicationcampaigns_srgt_id;
run;

data want;
set sum;
CampaignNumber = _n_;
keep Campaignname NBROFCAMPAIGNS CampaignNumber;
run;

Note that this is based on the data I had to make up; if you want code that works with your data, post it as a data step with datalines, like I did.