BookmarkSubscribeRSS Feed
yanshuai
Quartz | Level 8

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!

7 REPLIES 7
Reeza
Super User

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.

PGStats
Opal | Level 21

If both variables exist but one value is missing, use

 

group by coalesce(YEAR, FYEAR)

PG
yanshuai
Quartz | Level 8

No they are both existing. Just some datasets have YEAR only, some datasets have FYEAR only.

Can I still use this code?

PGStats
Opal | Level 21

Yes, precicely.

PG
Reeza
Super User

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
Reeza
Super User

@yanshuai wrote:

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. 

PGStats
Opal | Level 21

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;
PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 733 views
  • 2 likes
  • 3 in conversation