BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ksharp
Super User
Opps.

option  bufno=100 bufsize=128k;



chennupriya
Quartz | Level 8

Hi Ksharp ,

Sure I will try these options out and see if it increased efficiency

 

 

 

 

Thanks for all help

Ksharp
Super User
OR this maybe give you a little faster.







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;
options bufno=100 bufsize=128k;
data _null_;
 set temp end=last;
 call execute(cat('proc sql; create table x as select "cnt_id" as a length=20,"',
  _name_,'" as b length=20 from have(obs=1) union 
 select put(count(customerid),best. -l),put(',_name_,',best. -l) from have group by ',_name_));

if not last then call execute(cat('union select " "," " from have(obs=1)'));
call execute('order by 1 desc;quit;proc append base=want data=x force;run;');
run;

chennupriya
Quartz | Level 8

hi ,

Now the code runs very very fast

 

 

Thanks you so much and thanks a lot for all your help and now its running perfect and very fast

 

 

Thank you

chennupriya
Quartz | Level 8

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

Cynthia_sas
SAS Super FREQ

Hi:

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

count_each_value.png

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

chennupriya
Quartz | Level 8

hi Cynthia ,

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

 

 

 

Cynthia_sas
SAS Super FREQ
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
chennupriya
Quartz | Level 8

Hi Cynthia,

Yes all my varaibles which i want to have a proc tabulate are all numeric variables and no characters . I will try it out

 

 

 

 

Thanks for all help

Cynthia_sas
SAS Super FREQ

Hi:

  If you will need to preserve the name of the original variable, then you will need to use the VNAME function to capture that, as shown below. The PROC TABULATE step would be the same.

cynthia

  
** use fake names for the variables in the dataset;
** instead of numbered variables;   

   
data start;
infile datalines;
input customerid lucy    ricky ethel fred;
return;
datalines;
12345 	200 	300 	240 150
12345 	200 	300 	240 150
123 	100 	300 	626 200
121 	200 	300 	721 200
126 	150 	400 	666 200
;
run;
   
proc print data=start;
  title 'starting data';
run;
       
 ** need to list each variable in the ARRAY statement;              
data intermediate(keep=customerid pgtype val);
  set start;
  ** this array will be as big as needed for all variables that are listed;
  ** array name is just a convenient way to reference a group of variables;
  ** if need to keep the name of the original variable, use the VNAME function;   
  array prc (*) lucy ricky ethel fred;
  do I = 1 to dim(prc);
       pgtype = vname(prc(i));
       val = prc(i);
       output;
  end;
run;
** rest of program the same as previously posted;

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
  • 24 replies
  • 1766 views
  • 7 likes
  • 4 in conversation