DATA Step, Macro, Functions and more

Generate Crosstab in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Generate Crosstab in SAS

Hello Experts,

I am a beginner of SAS and need your kind help for a SAS programming question. Any advice will be greatly appreciated.

I have a sample data set as attachment: Customs purchase product A or B or both using either Credit card or Cash. I would like to summarize the data as a crosstab in SAS ---- show how many customs purchase product A only or product B only or product A and B using either credit card or cash by gender. Is that possible in SAS? Thank you very much for your time and help.

The crosstab I desire:

upload_2014-4-8_21-59-56.png


Accepted Solutions
Solution
‎09-06-2017 02:24 PM
Respected Advisor
Posts: 3,799

Re: Generate Crosstab in SAS

[ Edited ]
Posted in reply to qazwsxedc

Editor's Note: Thanks to @data_null__ and @Ksharp for both providing alternatives for creating the desired table.

 

How about this?

 
4-10-2014 2-46-23 PM.png
 
 
data have;
   input Custom$    Gender$    Product$    Payment &$16.;
  
cards;
Judi  Female   A  Credit Card
Judi  Female   B  Credit Card
Ben   Male  A  Cash
Tom   Male  A  Cash
Tom   Male  B  Cash
Bill  Male  B  Credit Card
Lindy Female   A  Cash
Shary Female   B  Credit Card
Judu  Female   A  Credit Card
Judu  Female   B  Credit Card
Billy Male  A  Cash
Tommy Male  A  Cash
Tommy Male  B  Cash
Benjum   Male  B  Credit Card
Linda Female   A  Cash
Shiry Female   B  Credit Card
;;;;
   run;
proc sort;
  
by custom gender payment product;
   run;
proc transpose data=have out=have2;
   by custom gender payment;
   var product;
   id product;
   run;
data have2;
   set have2;
   length Purchase $2;
   Purchase = cats(a,b);
  
drop _name_ a b;
   run;
proc summary data=have2 nway completetypes;
  
class Purchase gender payment;
   output out=counts;
   run;
proc print;
  
run;
proc tabulate data=counts;
   class Purchase gender payment;
   var _freq_;
   table purchase=' ' ,gender=' '*payment=' '*_freq_=' '*sum=' '*format=f8.0;
  
run;

View solution in original post


All Replies
Super User
Posts: 19,789

Re: Generate Crosstab in SAS

Posted in reply to qazwsxedc

I think you'll need to pre-process the data to calculate your counts, because its a count distinct. Hard to tell as your sample data doesn't match your sample output, and the image is too small to see.

I'd suggest providing sample data with sample output that correlates to the the data. Preferably in a data step, like below. Based on the data below, what would expect the output table to look like?

data have;

input Custom$    Gender$    Product$    Payment $;

cards;

Judi    Female    A    Credit Card

Judi    Female    B    Credit Card

Ben    Male    A    Cash

Tom    Male    A    Cash

Tom    Male    B    Cash

Bill    Male    B    Credit Card

Lindy    Female    A    Cash

Shary    Female    B    Credit Card

Judu    Female    A    Credit Card

Judu    Female    B    Credit Card

Billy    Male    A    Cash

Tommy    Male    A    Cash

Tommy    Male    B    Cash

Benjum    Male    B    Credit Card

Linda    Female    A    Cash

Shiry    Female    B    Credit Card

;

run;

Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

Hello Reeza,

Thank you very much for you reply.  Attached is the expected output (I manually count them) based on the sample data I attached in my original post.   Could you please help me out?  Thanks a lot!

FemaleMale
Credit CardCashCredit CardCash
Purchase A only0202
Purchase A and B2002
Purchase B only1020
Respected Advisor
Posts: 3,799

Re: Generate Crosstab in SAS

Posted in reply to qazwsxedc

I think for me it is easiest to create a new class variable that carries the Purchase info.

data have;
   input Custom$    Gender$    Product$    Payment &$16.;
  
cards;
Judi  Female   A  Credit Card
Judi  Female   B  Credit Card
Ben   Male  A  Cash
Tom   Male  A  Cash
Tom   Male  B  Cash
Bill  Male  B  Credit Card
Lindy Female   A  Cash
Shary Female   B  Credit Card
Judu  Female   A  Credit Card
Judu  Female   B  Credit Card
Billy Male  A  Cash
Tommy Male  A  Cash
Tommy Male  B  Cash
Benjum   Male  B  Credit Card
Linda Female   A  Cash
Shiry Female   B  Credit Card
;;;;
   run;
proc sort;
  
by custom gender payment product;
   run;
proc print;
  
run;
proc transpose data=have out=have2;
   by custom gender payment;
   var product;
   id product;
   run;
data have2;
   set have2;
   length Purchase $2;
   Purchase = cats(a,b);
  
drop _name_ a b;
   run;
proc print;
  
run;
proc summary data=have2 nway completetypes;
  
class Purchase gender payment;
   output out=counts;
   run;
proc print;
  
run;
Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

Posted in reply to data_null__

Hello data_null_,

Thank you so much for your reply.   Since I am a beginner of SAS, could you please show me how to load my attached sample data into your codes and run them?

In addition, could you please explain a little more about your following codes?  Thanks very much for your time and help

-------------------------------

data have2;
   set have2;
   length Purchase $2;
   Purchase = cats(a,b);
  
drop _name_ a b;
   run;

-------------------------------

Respected Advisor
Posts: 3,799

Re: Generate Crosstab in SAS

Posted in reply to qazwsxedc

You may be able to IMPORT the XLS with PROC IMPORT.  I'll leave that to you to research.  I just cut and pasted the data from EXCEL and translated the tabs to spaces so it could be read with"regular" list input.

You should look up the CATS function in the online documentation for a complete description.  And any of the other statement that are new to you also. 

As you can see the values of A are blank or A; and B are blank or B so if the subject does both the result is AB or if only one A or B.  So now you have the three categories you need to summarize.

Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

Posted in reply to data_null__

Thank you very much for your time and help....I really appreciate it...

Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

Posted in reply to data_null__

Hello data_null_,

I try to generate a crosstab output but failed.  I searched online but still cannot figure out what's wrong?  Please help me out.   Thanks...

----------------------- Codes I added ---------------

proc tabulate data=have2;

class Purchase gender payment;

table Purchase, gender*payment;

/*output out=counts;*/

run;

proc print;

   run;

-------------------------------------------------------------

------------------ Error message I got ------------

ERROR: Variable Purchase in list does not match type prescribed for this list.

-------------------------------------------------------------

Solution
‎09-06-2017 02:24 PM
Respected Advisor
Posts: 3,799

Re: Generate Crosstab in SAS

[ Edited ]
Posted in reply to qazwsxedc

Editor's Note: Thanks to @data_null__ and @Ksharp for both providing alternatives for creating the desired table.

 

How about this?

 
4-10-2014 2-46-23 PM.png
 
 
data have;
   input Custom$    Gender$    Product$    Payment &$16.;
  
cards;
Judi  Female   A  Credit Card
Judi  Female   B  Credit Card
Ben   Male  A  Cash
Tom   Male  A  Cash
Tom   Male  B  Cash
Bill  Male  B  Credit Card
Lindy Female   A  Cash
Shary Female   B  Credit Card
Judu  Female   A  Credit Card
Judu  Female   B  Credit Card
Billy Male  A  Cash
Tommy Male  A  Cash
Tommy Male  B  Cash
Benjum   Male  B  Credit Card
Linda Female   A  Cash
Shiry Female   B  Credit Card
;;;;
   run;
proc sort;
  
by custom gender payment product;
   run;
proc transpose data=have out=have2;
   by custom gender payment;
   var product;
   id product;
   run;
data have2;
   set have2;
   length Purchase $2;
   Purchase = cats(a,b);
  
drop _name_ a b;
   run;
proc summary data=have2 nway completetypes;
  
class Purchase gender payment;
   output out=counts;
   run;
proc print;
  
run;
proc tabulate data=counts;
   class Purchase gender payment;
   var _freq_;
   table purchase=' ' ,gender=' '*payment=' '*_freq_=' '*sum=' '*format=f8.0;
  
run;
Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

Posted in reply to data_null__

It works!  Thanks lot....

Super User
Posts: 10,028

Re: Generate Crosstab in SAS

Posted in reply to qazwsxedc

Easy for tabulate .

data have;
   input Custom$    Gender$    Product$    Payment &$16.; 
   cards; 
Judi  Female   A  Credit Card
Judi  Female   B  Credit Card
Ben   Male  A  Cash
Tom   Male  A  Cash
Tom   Male  B  Cash
Bill  Male  B  Credit Card
Lindy Female   A  Cash
Shary Female   B  Credit Card
Judu  Female   A  Credit Card
Judu  Female   B  Credit Card
Billy Male  A  Cash
Tommy Male  A  Cash
Tommy Male  B  Cash
Benjum   Male  B  Credit Card
Linda Female   A  Cash
Shiry Female   B  Credit Card
;;;;
   run; 
proc format;
value $fmt
 'A'='Purchase A only'
 'B'='Purchase B only'
 ;
run;
options missing=0;
proc tabulate data=have;
class custom gender product payment;
table product='' all='Purchase A and B',gender=''*payment=''*n=''*format=best8.0;
format product $fmt18.;
run;

Xia Keshan

Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

Keshan,

Thank you for your reply.  I run your codes and realize that customers who purchase both A and B are counted twice.  Do you know how to fix it?  Thanks in advance.

Super User
Posts: 5,504

Re: Generate Crosstab in SAS

Posted in reply to qazwsxedc

Customers are being counted twice because that's how you designed the table.  The programming only does what you asked it to do.  How would you like to redesign your table?  Would you like three columns, instead of two, for each gender (cash only, credit card only, and both cash and credit card)?  Entirely apart from writing a program, you have to design what you would like the output to be in cases where a customer used both purchasing methods.

Super User
Posts: 10,028

Re: Generate Crosstab in SAS

Posted in reply to qazwsxedc

Sorry. Not realize the problem is a little complicated.

data have;
   input Custom$    Gender$    Product$    Payment &$16.; 
   cards; 
Judi  Female   A  Credit Card
Judi  Female   B  Credit Card
Ben   Male  A  Cash
Tom   Male  A  Cash
Tom   Male  B  Cash
Bill  Male  B  Credit Card
Lindy Female   A  Cash
Shary Female   B  Credit Card
Judu  Female   A  Credit Card
Judu  Female   B  Credit Card
Billy Male  A  Cash
Tommy Male  A  Cash
Tommy Male  B  Cash
Benjum   Male  B  Credit Card
Linda Female   A  Cash
Shiry Female   B  Credit Card
;;;;
   run; 
proc sql;
create table x as
 select * from have group by custom,gender,payment having count(*)=1
  union corr
 select custom,gender,payment,'AB' as product from have group by custom,gender,payment having count(*)=2
 ;
quit;
proc format;
value $fmt
 'A'='Purchase A only'
 'B'='Purchase B only'
 'AB'='Purchase A and B'
 ;
run;
options missing=0;
proc tabulate data=x;
class gender product payment;
table product='',gender=''*payment=''*n=''*format=best8.0;
format product $fmt18.;
run;
 


Xia Keshan

Message was edited by: xia keshan

Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

Keshan,

That's great!   Thank you so much for your time and effort.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 8457 views
  • 3 likes
  • 5 in conversation