Why Proc SQL left join output duplicates values?

[xiangpang]

data eq;
input ID y x z w;
cards;
1 1 27 40 8
1 0 . 29 37
1 1 30 . 25
1 1 38 38 23
2 1 23 45 19
2 0 32 20 .
2 1 67 . .
2 1 . 27 .
3 0 33 23 46
3 1 21 12 56
3 0 78 . 34
3 1 13 45 . 
4 1 56 45 23
4 0 67 13 67  
4 0 . 35 13
4 1 48 35 56 
;
run;

proc freq data=eq;
tables id;
where z ne . ;
ods output CrossTabFreqs=CrossTabFreqs;
run;

Hello,

When I am trying to output the CrossTabFreqs. I could get a table but no output dataset is created. and I got the following message.

WARNING: Output 'CrossTabFreqs' was not created. Make sure that the output object name, label, or
path is spelled correctly. Also, verify that the appropriate procedure options are used
to produce the requested output object. For example, verify that the NOPRINT option is
not used.

Anyone could help me solve this problem? Thanks a lot

[stat_sas]

Hi,

This is because your proc freq syntax is not creating a cross tab. In order to get a cross tabulation in a data set then you have to generate a cross tab. Please try this to see the output data set

proc freq data=eq;
tables id*y;
where z ne . ;
ods output CrossTabFreqs=CrossTabFreqs;
run;

proc print data = CrossTabFreqs;

run;

[xiangpang]

thanks for your reply. I know it works if I use table ID*y . but what if I want to generate  a cross tab just for ID?

[stat_sas · Accepted solution]

For a single variable this is not a cross tab. This will be a frequency distribution of the variable id. Try this to get the freq distribution of variable id in a data set.

proc freq data=eq;
tables id;
where z ne . ;
ods output OneWayFreqs=OneWayFreqs;
run;

[xiangpang]

Thank you very much. That is what I want, but I don't know how to describe it. I think the following code is for frequency distribution. But it  is not what I want.

proc freq data=eq;
tables id/out=e1;
where z ne . ;
run;

[Tom]

A CrossTab is the tabulation of the cross between TWO variables.

You can't have a crosstab with only one variable, it doesn't make any sense.

If you want to see that ODS outputs are being generated by a particular piece of code you can use the ODS TRACE command.

But if you just want to get output from PROC FREQ there is no need to resort to ODS outputs. Just use the OUT= option on the TABLES statement.

[xiangpang]

thanks, "ods output OneWayFreqs=OneWayFreqs;" is what I want