topic Re: Generate Crosstab in SAS in SAS Programming

Generate Crosstab in SAS

qazwsxedc — Wed, 09 Apr 2014 16:56:33 GMT

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:

Re: Generate Crosstab in SAS

Reeza — Wed, 09 Apr 2014 18:09:03 GMT

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;

Re: Generate Crosstab in SAS

qazwsxedc — Wed, 09 Apr 2014 18:17:08 GMT

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

Re: Generate Crosstab in SAS

data_null__ — Wed, 09 Apr 2014 20:31:25 GMT

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; 

Re: Generate Crosstab in SAS

qazwsxedc — Wed, 09 Apr 2014 20:56:26 GMT

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;

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

Re: Generate Crosstab in SAS

data_null__ — Wed, 09 Apr 2014 21:19:40 GMT

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.

Re: Generate Crosstab in SAS

qazwsxedc — Thu, 10 Apr 2014 00:38:36 GMT

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

Re: Generate Crosstab in SAS

Ksharp — Thu, 10 Apr 2014 03:04:43 GMT

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

Re: Generate Crosstab in SAS

qazwsxedc — Thu, 10 Apr 2014 15:40:00 GMT

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.

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

Re: Generate Crosstab in SAS

qazwsxedc — Thu, 10 Apr 2014 15:43:42 GMT

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.

Re: Generate Crosstab in SAS

Astounding — Thu, 10 Apr 2014 16:47:35 GMT

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.

Re: Generate Crosstab in SAS

data_null__ — Wed, 06 Sep 2017 18:23:48 GMT

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;

Re: Generate Crosstab in SAS

qazwsxedc — Thu, 10 Apr 2014 20:19:28 GMT

It works! Thanks lot....

Re: Generate Crosstab in SAS

Ksharp — Fri, 11 Apr 2014 03:43:22 GMT

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

Re: Generate Crosstab in SAS

qazwsxedc — Fri, 11 Apr 2014 13:38:17 GMT

Keshan,

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

Re: Generate Crosstab in SAS

Ksharp — Fri, 11 Apr 2014 14:27:27 GMT

There is one thing I am worried about.

Judu Female A Credit Card

Judu Female B Cash

what you are going to count ?

Xia Keshan

Re: Generate Crosstab in SAS

qazwsxedc — Fri, 11 Apr 2014 14:43:10 GMT

Keshan,

Thank you so much for your help. One more thing I really need your kind help.

I try to import the sample file (attached in my original post) into SAS but failed. Could you please let me know what's wrong are my codes? Thanks a lot.

---------------- Codes I used ------------------

data work.mydata

infile 'C:\SAS\Output\Customer Sample.csv';

informat Custom $10.;

informat Gender $10.;

informat Product $10.;

informat Payment $10.;

input

Custom $

Gender $

Product $

Payment $

run;

proc contents data=work.mydata;

run;

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

---------- Error message ---------

ERROR: No DATALINES or INFILE statement.

ERROR: Extension for physical file name "C:\SAS\Output\Customer Sample.csv" does not correspond

to a valid member type.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.MYDATA may be incomplete. When this step was stopped there were 0

observations and 5 variables.

WARNING: The data set WORK.INFILE may be incomplete. When this step was stopped there were 0

observations and 5 variables.

WARNING: Data set WORK.INFILE was not replaced because this step was stopped.

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

Re: Generate Crosstab in SAS

Ksharp — Fri, 11 Apr 2014 15:07:18 GMT

data work.mydata ;

you missed ';'