03-01-2018 05:13 PM
I am trying to capture a certain variable according to certain variable.
data have ; input YEAR SALE ID $5.; datalines; 2001 12 ab34 2001 10 1234 2001 14 ab34 2001 10 2234 2002 10 1234 2002 10 ab34 2002 10 2234; run;
Some dataset has variable YEAR, but some has FYEAR, how can I group by YEAR, and group by FYEAR when YEAR is missing?
Can they be written in the GROUP BY code?
proc sql; create table want as SUM(SALE) as TOTALSALE from have group by YEAR or FYEAR; /*How can I realize this step using code? SQL did not recgonize OR*/ quit;
Thank you very much!
03-01-2018 05:15 PM
If the variable doesn't exist that's problematic.
I would suggest using a macro to check if it exists and if it does uses the correct variable. Or rename them all to be standardized.
03-01-2018 10:44 PM
No, if the variable doesn't exist it won't work.
69 proc sql; 70 create table want as 71 select * 72 from sashelp.class 73 order by coalesce(age, new_age); ERROR: The COALESCE function requires its arguments to be of the same data type. ERROR: The following columns were not found in the contributing tables: new_age. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 74 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
03-01-2018 10:46 PM
No they are both existing. Just some datasets have YEAR only, some datasets have FYEAR only.
Can I still use this code?
@PGStats is asking if they both exist in the same data set, you answer seems to indicate that it doesn't, therefore coalesce will not work.
03-01-2018 11:12 PM
You can force both variables to exist with an extra step such as:
data have ; input YEAR SALE ID $5.; datalines; 2001 12 ab34 2001 10 1234 2001 14 ab34 2001 10 2234 2002 10 1234 2002 10 ab34 2002 10 2234 ; data both; length year fyear 8; set have; run; proc sql; create table want as select coalesce(YEAR, FYEAR) as year, SUM(SALE) as TOTALSALE from both group by calculated year; quit;