Hi i am having a data set city i wnat to give no if the city is the same and should produce 1 new number if it is blank
data temp;
input city $;
cards;
hyb
vja
nlg
mum
pune
pune
hyb
vja
triven
run;
output:
city no
hyb 1
vja 2
nlg 3
4
mum 5
pune 6
pune 6
7
hyb 1
8
vja 2
9
triven 10
As usual, there are a number of ways to accomplish what you want. e.g.:
data temp (drop=citie: number);
array cities(99999) $;
infile cards truncover;
input city $;
retain cities;
if city ne "" then do;
if whichc(city,of cities
no=whichc(city,of cities
else do;
number+1;
cities(number)=city;
no=number;
end;
end;
else do;
number+1;
no=number;
end;
cards;
hyb
vja
nlg
mum
pune
pune
hyb
vja
triven
;
run;
Thqs it worked but when i added set statement for the blank nubers the no is not generatring
data temp1 (drop=citie: number);
set temp;
array cities(99999) $;
retain cities;
if city ne "" then do;
if whichc(city,of cities
no=whichc(city,of cities
else do;
number+1;
cities(number)=city;
no=number;
end;
end;
else do;
number+1;
no=number;
end;
run;
proc print;
run;
But i want the no for blank values also..
output:
Obs city no
1 hyb 1
2 vja 2
3 nlg 3
4 mum 4
5 pune 5
6 pune 5
7 hyb 1
8 vja 2
9 triven 6
For your situation. Hash table is the best choice.
How about:
data temp; infile datalines truncover; input city $ ; datalines; hyb vja nlg mum pune pune hyb vja triven sdsd ; run; data want(drop=rc x _count ); set temp; retain count _count; if _n_ eq 1 then do; declare hash ha(hashexp:10); ha.definekey('city'); ha.definedata('_count'); ha.definedone(); end; if not missing(city) then do;rc=ha.find();count=_count;end; if rc ne 0 and not missing(city) then do;x+1;count=x;_count=x;ha.add(); end; if missing(city) and not missing(lag(city)) then do;x+1; count=x;end; run;
Ksharp
data temp1 (drop=citie: number);
set temp;
array cities(99999) $;
retain cities;
if city eq "" then do;
number+1;
no=number;
end;
else if city ne "" then do;
if whichc(city,of cities
no=whichc(city,of cities
else do;
number+1;
cities(number)=city;
no=number;
end;
end;
else do;
number+1;
no=number;
end;
run;
Hi. Art.T
I know your code is correct.
Ksharp
Hi ... another idea with just the CATX and FINDW functions ...
data temp;
input city $8. ;
cards;
hyb
vja
nlg
mum
pune
pune
hyb
vja
triven
run;
data x;
length x $1000;
retain x;
set temp;
select;
when (missing(city)) do; x = catx(' ',x,'miss'); nc+1; count=nc; end;
when (^findw(x,trim(city))) do; x = catx(' ',x,city); nc+1; count=nc; end;
otherwise count = findw(x,trim(city),' ','e');
end;
keep city count;
run;
proc print data=x noobs;
run;
city count
hyb 1
vja 2
nlg 3
4
mum 5
pune 6
pune 6
7
hyb 1
8
vja 2
9
triven 10
Hi. If Op has lots of cities, you can not do it . because x can not hold all the cities, the max length of x is 32767.
Ksharp
Hi ... there's always "y" and "z" once "x" reaches a certain size ... or your hash object.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.