summary statistics

[chennupriya]

Hi ,

I have a dataset xyz  like below which has customer id and pricing attributes . I want to check how many ids are assigned 200 of pricing 01

the report should be like for example and can i write a loop for this ?

cnt_id	pricing01
3	200
1	100
1	150
cnt_id	pricing02
4	300
1	400
cnt_id	pricing03
2	240
1	626
1	721
1	666

customerid	pricing01	pricing02	pricing03
12345	200	300	240
12345	200	300	240
123	100	300	626
121	200	300	721
126	150	400	666

[mkeintz]

Do you require the non-sorted order of pricing values within each pricing group?  It appears you want the same order as the values are first encountered, right?

[chennupriya]

hi

Any order of pricing values should be fine but i need how many ids when pricing01 is 200 and how many i have when pricing01 is 100 and how maany ids i have when pricing01 is 150 same way for pricing02

Thanks

[mkeintz]

Let PROC FREQ do the work.  This program has "ORDER=DATA" which present counts in order that the values appear.  Take it out  if you want ascending order for each variable.

Notes:

1. If you want results to go somewhere other than the log, put a FILE statement in the data step.
2. You might to drop the "NOPRINT" option to see what proc freq prints directly.

regards,

Mark

data have;
input customerid pricing01 pricing02 pricing03 ;
datalines;
12345 200 300 240 
12345 200 300 240 
123 100 300 626 
121 200 300 721 
126 150 400 666 
run;

proc freq data=have order=data noprint;
  table pricing01 / out=priceing01 ;
  table pricing02 / out=priceing02 ;
  table pricing03 / out=priceing03 ;
run;
data _null_;
  set priceing: indsname=inw;

  array prc{*} pricing: ;
  if inw^=lag(inw) then do;
     p+1;
     vnam=vname(prc{p});
     put 'CNT_ID '  vnam;
  end;
  put count 6. +3 prc{p} 3.;
run;

[Ksharp · Accepted solution]

How about this one.



data have;
infile cards truncover expandtabs;
input customerid	pricing01	pricing02	pricing03;
cards;
12345	200	300	240
12345	200	300	240
123	100	300	626
121	200	300	721
126	150	400	666
;
run;



proc sql;
create table want as
select 1 as x,'cnt_id' as a length=12,'pricing01' as b length=12
 from have(obs=1)
union
select 2,put(count(customerid),best8. -l) ,
       put(pricing01,best8. -l)
 from have
  group by pricing01 
union
select 3,' ',' '
 from have(obs=1)
union
select 4 as x,'cnt_id' as a length=12,'pricing02' as b length=12
 from have(obs=1)
union
select 5,put(count(customerid),best8. -l) ,
       put(pricing02,best8. -l)
 from have
  group by pricing02 
union
select 6,' ',' '
 from have(obs=1)
union
select 7 as x,'cnt_id' as a length=12,'pricing03' as b length=12
 from have(obs=1)
union
select 8,put(count(customerid),best8. -l) ,
       put(pricing03,best8. -l)
 from have
  group by pricing03 
 ;
quit;
proc report data=want nowd noheader;
column a b;
run;

[chennupriya]

Hi Ksharp,
Can I get something easy code like
Proc sql;
create table xy as
select count(customerid),
pricing01
from xyz
group by pricing01
;
quit;

but I have 100's of variables so I want this to be in macro so that each time when it loops it would create a table for each pricing item

and can we use proc summary for the report to look in the format the mentioned

Thanks


Thanks

[Ksharp]

data have;
infile cards truncover expandtabs;
input customerid	pricing01	pricing02	pricing03;
cards;
12345	200	300	240
12345	200	300	240
123	100	300	626
121	200	300	721
126	150	400	666
;
run;

proc transpose data=have(keep=pricing: obs=0) out=temp;
run;
data temp;
 set temp;
 retain n -2;
 n=n+3;
run;

data _null_;
 set temp end=last;
 if _n_=1 then call execute('proc sql;create table want as ');
 call execute(cat('select ',n,' as x,"cnt_id" as a length=12,"',
 _name_,'" as b length=12 from have(obs=1) union select ',n+1,
 ',put(count(customerid),best8. -l),put(',_name_,',best8. -l) from have group by ',
 _name_));
if not last then call execute(cat('union select ',n+2,'," "," " from have(obs=1) union'));
else call execute(';quit;');
run;
proc report data=want nowd noheader;
column a b;
run;

[chennupriya]

Hi Ksharp,

The code worked perfectly but it works only when i have few records in 1000 but when i have 2 lakh records in the data set have

the below step is taking very long time is there any other other code so that it takes very less time  Can u please help

 data _null_;
set temp end=last;
if _n_=1 then call execute('proc sql;create table want as ');
call execute(cat('select ',n,' as x,"cnt_id" as a length=12,"',
_name_,'" as b length=12 from have(obs=1) union select ',n+1,
',put(count(customerid),best8. -l),put(',_name_,',best8. -l) from have group by ',
_name_));
if not last then call execute(cat('union select ',n+2,'," "," " from have(obs=1) union'));
else call execute(';quit;');
run;

Thanks for all your help

[Ksharp]

Sorry.
I think time is wasted on PUT().
Do you have to make a table contain these, which make variable have to be character type.
Can't you just create a report to get it ?Like:
proc sql;
select count(*) as cust_id,price_01
 from have
  group by price_01
union all
select count(*) as cust_id,price_02
 from have
  group by price_02
..................................


Another way is using proc means/proc freq ,
But that need make a macro firstly and need more code too.

Or IML code if your memory is big enough.

[chennupriya]

Hi Ksharp,

Its ok if the code is big and no IML and i dont need to make my variables character and we can remove put from the code

I tried to remove put and run it but I am getting the following error with below code

+ proc sql;

1 + create table test77 as

NOTE: Line generated by the CALL EXECUTE routine.

2 + select 1 as x,"cnt_id" as a length=12,"Pricing01 " as b length=100 from test7(obs=1) union select

2 !+2,count(customerid),_name_, from test7 group by pricing01

_____

22

76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =,

>, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE,

LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Code I have used

data _null_;

 set temp end=last;

 if _n_=1 then call execute('proc sql;create table want as ');

 call execute(cat('select ',n,' as x," cnt_id" as a length=12,"',

 _name_,'" as b length=100 from have(obs=1) union select ',n+1,

 ',count(customerid),_name_, from have group by ',

 _name_));

if not last then call execute(cat('union select ',n+2,'," "," " from have (obs=1) union'));

else call execute(';quit;');

run;

Thanks for your help

[chennupriya]

Hi Ksharp ,

Is there any possibility that you could remove put and then send code again because i tried to remove it but i am getting syntax errors for data_null_ step

Can u please help

Thanks

[Ksharp]

The question is do you have to make a table ? that would lead to have to use PUT() 
which would be very slowly.

Or just want make a report as Cynthia did, that would be very fast.

[Ksharp]

The following  just generated a report ,should be very fast.





data have;
infile cards truncover expandtabs;
input customerid	pricing01	pricing02	pricing03;
cards;
12345	200	300	240
12345	200	300	240
123	100	300	626
121	200	300	721
126	150	400	666
;
run;

proc transpose data=have(keep=pricing: obs=0) out=temp;
run;

data _null_;
 set temp end=last;
 if _n_=1 then call execute('proc sql; ');
 call execute(cat('select count(customerid) as cust_id,',_name_,
 ' from have group by ',_name_,';'));
if last then call execute(';quit;');
run;

[chennupriya]

Hi Ksharp,

yes but the code doesnot union and  create all the combinations in a single dataset like your code before same excatly like below  but  i dont need any put as it will take lot of time . Cynthia's report also i have ran it but i need  a dataset  instead of report

cnt_id	pricing01
3	200
1	100
1	150
cnt_id	pricing02
4	300
1	400
cnt_id	pricing03
2	240
1	626
1	721
1	666

Thanks for all help

[Ksharp]

Then you have no choice. you have to use PUT() , no matter what method I used in SQL or DataStep ,HashTable, IML ....

Add the following maybe a faster.

options bulknum=100 bulksize=128k ;