BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
qazwsxedc
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

17 REPLIES 17
Reeza
Super User

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
Calcite | Level 5

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
data_null__
Jade | Level 19

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
Calcite | Level 5

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__
Jade | Level 19

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
Calcite | Level 5

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

qazwsxedc
Calcite | Level 5

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__
Jade | Level 19

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;
qazwsxedc
Calcite | Level 5

It works!  Thanks lot....

Ksharp
Super User

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
Calcite | Level 5

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
PROC Star

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
Super User

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
Calcite | Level 5

Keshan,

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 17 replies
  • 23551 views
  • 3 likes
  • 5 in conversation