The SAS Output Delivery System and reporting techniques

Why Proc SQL left join output duplicates values?

Accepted Solution Solved
Reply
Highlighted
Contributor
Posts: 62
Accepted Solution

Why Proc SQL left join output duplicates values?

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


Accepted Solutions
Solution
‎03-03-2018 01:08 PM
Trusted Advisor
Posts: 1,270

Re: Why Proc SQL left join output duplicates values?

Posted in reply to xiangpang

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,270

Re: Why Proc SQL left join output duplicates values?

Posted in reply to xiangpang

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;

Contributor
Posts: 62

Re: Why Proc SQL left join output duplicates values?

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?

Solution
‎03-03-2018 01:08 PM
Trusted Advisor
Posts: 1,270

Re: Why Proc SQL left join output duplicates values?

Posted in reply to xiangpang

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;

Contributor
Posts: 62

Re: Why Proc SQL left join output duplicates values?

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;

Super User
Super User
Posts: 8,073

Re: Why Proc SQL left join output duplicates values?

Posted in reply to xiangpang

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.

Contributor
Posts: 62

Re: Why Proc SQL left join output duplicates values?

thanks, "ods output OneWayFreqs=OneWayFreqs;" is what I want
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 293 views
  • 0 likes
  • 3 in conversation