BookmarkSubscribeRSS Feed
NagendraBS
Fluorite | Level 6

Hi.,

I have a dataset people with cust_id and landline_no variables

cust_id landline

101      0802471852

102      0405214752

101      0226510020

103      0446125420

i have another dataset state with cust_id and state_code

city   std_code

bang   080

hyd     040

mum   022

chn     044

i need the count of customer with respect to std_code as result

std_code  cust_cnt

080           2

-               -

-               -

thanks in advance

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Not sure about your title (Dataset solution) does that mean it needs to be only using datasteps?  If so then you need to do each part separately and merge them back together.  Alternatively you could do:

data have;
cust_id="101"; landline="0802471852"; output;
cust_id="106"; landline="0802471852"; output;
cust_id="102"; landline="0405214752"; output;
cust_id="101"; landline="0226510020"; output;
cust_id="103"; landline="0446125420"; output;
run;
data code;
city="bang"; std_code="080"; output;
city="hyd"; std_code="040"; output;
city="mum"; std_code="022"; output;
run;

proc sql;
  create table WANT as
  select  A.CUST_ID,
          A.LANDLINE,
          B.STD_CODE,
          C.COUNT
  from    HAVE A
  left join CODE B
  on      substr(A.LANDLINE,1,3)=B.STD_CODE
  left join (select substr(LANDLINE,1,3) as CDE,
                    count(1) as COUNT
             from   HAVE
             group by CDE) C
  on      substr(A.LANDLINE,1,3)=C.CDE;
quit;

NagendraBS
Fluorite | Level 6

is it possible to do using merge

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, its just long winded.  The logic above applies.  Just do each bit separately.  E.g. sort have, datastep to create 3 char std_code.  Sort codes, then datastep merge that and codes by std_code.  With that dataset, sort by std_code, then in a datastep have a retain statement for cont, or do a proc means.  The means output N can then be merged back on. 

Ksharp
Super User

Is std_code equivalent with the first three character of landline ?

data have;
cust_id="101"; landline="0802471852"; output;
cust_id="106"; landline="0802471852"; output;
cust_id="102"; landline="0405214752"; output;
cust_id="101"; landline="0226510020"; output;
cust_id="103"; landline="0446125420"; output;
run;
data code;
city="bang"; std_code="080"; output;
city="hyd"; std_code="040"; output;
city="mum"; std_code="022"; output;
run;
proc freq noprint data=have(rename=(landline=std_code));
tables std_code/out=want(drop=percent) norow nocol nopercent nocum;
format std_code $3.;
run;

Xia Keshan

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 807 views
  • 7 likes
  • 3 in conversation