Generate Crosstab in SAS

[qazwsxedc]

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:

[Reeza]

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;

[qazwsxedc]

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!

	Female		Male
	Credit Card	Cash	Credit Card	Cash
Purchase A only	0	2	0	2
Purchase A and B	2	0	0	2
Purchase B only	1	0	2	0

[data_null__]

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;

[qazwsxedc]

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;

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

[data_null__]

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.

[qazwsxedc]

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

[qazwsxedc]

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.

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

[data_null__ · Accepted solution]

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

How about this?

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;

[qazwsxedc]

It works!  Thanks lot....

[Ksharp]

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

[qazwsxedc]

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.

[Astounding]

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.

[Ksharp]

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

[qazwsxedc]

Keshan,

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