Base SAS question for two datasets

[chandu1]

Please have a look into below 

data a;
infile cards;
input cardtype ar ncust ;
cards;
271 350 10001
271 680 10002
371 720 10003
371 980 10004
203 1002 10001
203 401 10005
304 3050 10003
;
run;

data b;
infile cards;
input ncust age sex $;
cards;
10001 28 M
10002 29 F
10003 30 M
10004 31 F
10005 32 F
run;

How do we write the code for to get the following output result using the above sas datasets a and b

Result:

„ƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ†

|        |                         Sex                         |

|        |--------------------------|--------------------------|

|        |          Female          |           Male           |

|        |--------|--------|--------|--------|--------|--------|

|        | No.of  |        |        | No.of  |        |        |

|        |  A/Cs  |   AR   |   %    |  A/Cs  |   AR   |   %    |

|--------|--------|--------|--------|--------|--------|--------|

|ncust   |        |        |        |        |        |        |

|--------|        |        |        |        |        |        |

|10001   |       .|       .|       .|       2|   1,352|    26.4|

|--------|--------|--------|--------|--------|--------|--------|

|10002   |       1|     680|    33.0|       .|       .|       .|

|--------|--------|--------|--------|--------|--------|--------|

|10003   |       .|       .|       .|       2|   3,770|    73.6|

|--------|--------|--------|--------|--------|--------|--------|

|10004   |       1|     980|    47.5|       .|       .|       .|

|--------|--------|--------|--------|--------|--------|--------|

|10005   |       1|     401|    19.5|       .|       .|       .|

|--------|--------|--------|--------|--------|--------|--------|

|Total   |       3|   2,061|   100.0|       4|   5,122|   100.0|

|--------|--------|--------|--------|--------|--------|--------|

[ErikLund_Jensen]

First a data step to merge a and b, and then a proc tabulate to present the result.

[ballardw · Accepted solution]

---

@chandu1 wrote:

Please have a look into below 

How do we write the code for to get the following output result using the above sas datasets a and b

Result:

„ƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ†

|        |                         Sex                         |

|        |--------------------------|--------------------------|

|        |          Female          |           Male           |

|        |--------|--------|--------|--------|--------|--------|

|        | No.of  |        |        | No.of  |        |        |

|        |  A/Cs  |   AR   |   %    |  A/Cs  |   AR   |   %    |

|--------|--------|--------|--------|--------|--------|--------|

|ncust   |        |        |        |        |        |        |

|--------|        |        |        |        |        |        |

|10001   |       .|       .|       .|       2|   1,352|    26.4|

What is an "A/C"?  How do we tell from your data? What  statistic is requested for AR? % of what?

Guessing for answers to above:

data a;
infile cards;
input cardtype ar ncust ;
cards;
271 350 10001
271 680 10002
371 720 10003
371 980 10004
203 1002 10001
203 401 10005
304 3050 10003
;
run;

data b;
infile cards;
input ncust age sex $;
cards;
10001 28 M
10002 29 F
10003 30 M
10004 31 F
10005 32 F
run;
proc sort data=a;
  by ncust;
run;
proc sort data=b;
  by ncust;
run;

data want;
  merge a b;
  by ncust;
run;

proc tabulate data=want;
   class ncust sex;
   var ar;
   table ncust all='Total',
         sex * ar=''*(n='# A/C' sum='AR'*f=comma6. colpctsum='%'*f=5.1)
         /misstext=' '
   ;
run;

If you want "Female" instead of "F" you need to create custom format to display the text you want and associate that format with the SEX variable.

If you are going to paste text from the OUTPUT window open a code box with the forum's {I} or "running man" icon to keep the message windows from reformatting text.

[chandu1]

Thanks for your help