topic Re: Why Proc SQL left join output duplicates values? in ODS and Base Reporting

Why Proc SQL left join output duplicates values?

xiangpang — Sat, 03 Mar 2018 17:13:28 GMT

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

Re: Why Proc SQL left join output duplicates values?

stat_sas — Sat, 03 Mar 2018 17:43:29 GMT

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;

Re: Why Proc SQL left join output duplicates values?

xiangpang — Sat, 03 Mar 2018 17:50:58 GMT

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?

Re: Why Proc SQL left join output duplicates values?

stat_sas — Sat, 03 Mar 2018 17:58:50 GMT

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;

Re: Why Proc SQL left join output duplicates values?

xiangpang — Sat, 03 Mar 2018 18:11:31 GMT

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;

Re: Why Proc SQL left join output duplicates values?

Tom — Sun, 04 Mar 2018 03:47:34 GMT

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.

Re: Why Proc SQL left join output duplicates values?

xiangpang — Sun, 04 Mar 2018 04:14:16 GMT

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