DATA Step, Macro, Functions and more

Generate Crosstab in SAS

Reply
Occasional Contributor
Posts: 9

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

Super User
Posts: 17,824

Re: Generate Crosstab in SAS

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,777

Re: Generate Crosstab in SAS

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

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,777

Re: Generate Crosstab in SAS

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

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

Occasional Contributor
Posts: 9

Re: Generate Crosstab in SAS

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.

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

Respected Advisor
Posts: 3,777

Re: Generate Crosstab in SAS

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

It works!  Thanks lot....

Super User
Posts: 9,681

Re: Generate Crosstab in SAS

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,082

Re: Generate Crosstab in SAS

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: 9,681

Re: Generate Crosstab in SAS

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.

Ask a Question
Discussion stats
  • 17 replies
  • 6880 views
  • 3 likes
  • 5 in conversation