topic Re: summary statistics in SAS Procedures

summary statistics

chennupriya — Wed, 23 Nov 2016 01:00:45 GMT

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

Re: summary statistics

mkeintz — Wed, 23 Nov 2016 01:46:10 GMT

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?

Re: summary statistics

chennupriya — Wed, 23 Nov 2016 01:53:16 GMT

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

Re: summary statistics

Ksharp — Wed, 23 Nov 2016 03:40:05 GMT

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;

Re: summary statistics

mkeintz — Wed, 23 Nov 2016 13:04:38 GMT

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:

If you want results to go somewhere other than the log, put a FILE statement in the data step.
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;

Re: summary statistics

chennupriya — Wed, 23 Nov 2016 13:56:01 GMT

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

Re: summary statistics

Ksharp — Thu, 24 Nov 2016 04:28:58 GMT




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;

Re: summary statistics

chennupriya — Tue, 29 Nov 2016 23:12:28 GMT

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

Re: summary statistics

chennupriya — Tue, 29 Nov 2016 22:59:13 GMT

as we are using group by for a large dataset its taking time can u pls help if there is an efficient way

Thanks for your help

Re: summary statistics

Ksharp — Wed, 30 Nov 2016 09:31:27 GMT

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.

Re: summary statistics

Cynthia_sas — Wed, 30 Nov 2016 14:23:47 GMT

Hi:

It seems to me that PROC TABULATE and transposed data might be better suited and faster. For example, TABULATE can produce this:

no matter how many variables named "pricingxx" you have -- once the data is transposed. If the variables aren't named the same, you might have a little more to declare in the array statement, but as of now, I don't see that a macro is necessary.

Here's the code:

data start;
  infile datalines;
  input customerid 	pricing01 	pricing02 	pricing03;
return;
datalines;
12345 	200 	300 	240
12345 	200 	300 	240
123 	100 	300 	626
121 	200 	300 	721
126 	150 	400 	666
;
run;
 
proc print data=start;
  title 'starting data';
run;
 
data intermediate(keep=customerid pgtype val);
  set start;
  ** this array will be as big as needed for all variables that start with 'pricing';
  ** assumes that the variables are numbered 01, 02, 03, etc in original data;
  array prc (*) pricing:;
  do I = 1 to dim(prc);
        pgtype = catt('Pricing',put(i,z2.));
	val = prc(i);
	output;
  end;
run;
 
proc print data=intermediate;
title 'intermediate to send to tabulate';
run;
 
proc tabulate data=intermediate;
  title 'Count customers for each value';
  class pgtype ;
  class val / order=freq;
  table pgtype=' ',
        val=' ', 
        n='Count' / box=_page_ ;
run;

cynthia

Re: summary statistics

chennupriya — Wed, 30 Nov 2016 19:11:16 GMT

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

_____

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

Re: summary statistics

chennupriya — Wed, 30 Nov 2016 19:40:15 GMT

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

Re: summary statistics

Ksharp — Thu, 01 Dec 2016 08:29:32 GMT

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.

Re: summary statistics

Ksharp — Thu, 01 Dec 2016 08:44:03 GMT

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;

Re: summary statistics

chennupriya — Thu, 01 Dec 2016 19:23:30 GMT

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

Re: summary statistics

chennupriya — Thu, 01 Dec 2016 19:42:46 GMT

hi Cynthia ,

I have a question what if i have all different varaibles like item ,price ,insurance,name ?

Re: summary statistics

Cynthia_sas — Thu, 01 Dec 2016 23:21:15 GMT

then you would need to list them in the array statement...but item, and name are character aren't they? price and insurance would be numeric? An array can only contain one type of variable (either character or numeric) -- that is different than the original scope of your question.

cynthia

Re: summary statistics

Ksharp — Fri, 02 Dec 2016 10:23:11 GMT

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 ;

Re: summary statistics

Ksharp — Fri, 02 Dec 2016 11:15:37 GMT

Opps.

option  bufno=100 bufsize=128k;