BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
iliyan
Obsidian | Level 7

Hello Everyone,

 

I am trying to convince observations into one like per data set (unique 'ident' label). The code here works, but for some reason, it does not do the final set of observations for year2013. However, if I shorten down the proc means var and sum ignoring some observations I have it works just fine.

 

I know the code is not the most efficient but I have only been working with this program for 2 months, is there just a simple rule I am overlooking when using proc means?

 

Also attached is a sample set of data for your reference.

 

data db.ebcollector;
set db.ebcollectorint;
if year = 2000 then do;
AADT_00 = AADT;
iri_00 = iri;
TOT_00 = TOT;
FI_00 = FI;
PD_00 = PD; 

end;
if year = 2001 then do;
AADT_01 = AADT;
iri_01 = iri;
TOT_01 = TOT;
FI_01 = FI;
PD_01 = PD; 

end;
if year = 2002 then do;
AADT_02 = AADT;
iri_02 = iri;
TOT_02 = TOT;
FI_02 = FI;
PD_02 = PD;

end;
if year = 2003 then do;
AADT_03 = AADT;
iri_03 = iri;
TOT_03 = TOT;
FI_03 = FI;
PD_03 = PD; 

end;
if year = 2004 then do;
AADT_04 = AADT;
iri_04 = iri;
TOT_04 = TOT;
FI_04 = FI;
PD_04 = PD; 

end;
if year = 2005 then do;
AADT_05 = AADT;
iri_05 = iri;
TOT_05 = TOT;
FI_05 = FI;
PD_05 = PD; 

end;
if year = 2006 then do;
AADT_06 = AADT;
iri_06 = iri;
TOT_06 = TOT;
FI_06 = FI;
PD_06 = PD; 

end;
if year = 2007 then do;
AADT_07 = AADT;
iri_07 = iri;
TOT_07 = TOT;
FI_07 = FI;
PD_07 = PD; 

end;
if year = 2008 then do;
AADT_08 = AADT;
iri_08 = iri;
TOT_08 = TOT;
FI_08 = FI;
PD_08 = PD; 

end;
if year = 2009 then do;
AADT_09 = AADT;
iri_09 = iri;
TOT_09 = TOT;
FI_09 = FI;
PD_09 = PD; 

end;
if year = 2010 then do;
AADT_10 = AADT;
iri_10 = iri;
TOT_10 = TOT;
FI_10 = FI;
PD_10 = PD; 

end;
if year = 2011 then do;
AADT_11 = AADT;
iri_11 = iri;
TOT_11 = TOT;
FI_11 = FI;
PD_11 = PD; 

end;
if year = 2012 then do;
AADT_12 = AADT;
iri_12 = iri;
TOT_12 = TOT;
FI_12 = FI;
PD_12 = PD;

end;
if year = 2013 then do;
AADT_13 = AADT;
iri_13 = iri;
TOT_13 = TOT;
FI_13 = FI;
PD_13 = PD;

end;
run;
proc sort;
by ident lengthn;
run;
proc means data=db.ebcollector noprint; by ident lengthn; var act AADT_00 iri_00 TOT_00 FI_00 PD_00 aadt_01 iri_01 tot_01 fi_01 pd_01 aadt_02 iri_02 tot_02 fi_02 pd_02 aadt_03 iri_03 tot_03 fi_03 pd_03 aadt_04 iri_04 tot_04 fi_04 pd_04 aadt_05 iri_05 tot_05 fi_05 pd_05 aadt_06 iri_06 tot_06 fi_06 pd_06 aadt_07 iri_07 tot_07 fi_07 pd_07 aadt_08 iri_08 tot_08 fi_08 pd_08 aadt_09 iri_08 tot_08 fi_08 pd_08 aadt_09 iri_09 tot_09 fi_09 pd_09 aadt_10 iri_10 tot_10 fi_10 pd_10 aadt_11 iri_11 tot_11 fi_11 pd_11 
aadt_12 iri_12 tot_12 fi_12 pd_12 aadt_13 iri_13 tot_13 fi_13 pd_13;

output out=db.ebcollectorsum sum=act AADT_00 iri_00 TOT_00 FI_00 PD_00 aadt_01 iri_01 tot_01 fi_01 pd_01 aadt_02 iri_02 tot_02 fi_02 pd_02 aadt_03 iri_03 tot_03 fi_03 pd_03 aadt_04 iri_04 tot_04 fi_04 pd_04 aadt_05 iri_05 tot_05 fi_05 pd_05 aadt_06 iri_06 tot_06 fi_06 pd_06 aadt_07 iri_07 tot_07 fi_07 pd_07 aadt_08 iri_08 tot_08 fi_08 pd_08 aadt_09 iri_08 tot_08 fi_08 pd_08 aadt_09 iri_09 tot_09 fi_09 pd_09 aadt_10 iri_10 tot_10 fi_10 pd_10 aadt_11 iri_11 tot_11 fi_11 pd_11 
aadt_12 iri_12 tot_12 fi_12 pd_12 aadt_13 iri_13 tot_13 fi_13 pd_13;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

The safest way is to cut back on the OUTPUT statement:

 

output out=db.ebcollectorsum sum=;

 

The names that follow SUM= go in order.  The first name after SUM= becomes the sum of the first variable in the VAR statement.  Second name after SUM= becomes the sum of the second variable in the VAR statement.  Instead of making sure you have a match all the way through both sets of names, use the OUTPUT statement above.  It tells SAS to re-use the incoming variable names, making them the SUM in the output data set.  That trick only works when you are generating a single statistic (such as SUM only, but not SUM and MEAN at the same time for example).

 

Also note a better strategy ... don't use a DATA step at all.  You already have YEAR in the data set.  It's easy to tell PROC MEANS to compute a separate set of sums for each YEAR:

 

proc means data=db.ebcollector nway;

class year;

var aadt iri tot fi pd;

output out=db.ebcollectorsum sum=;

run;

 

You you will get a separate observation in the output data set for each year.  It's a good idea to learn how to program with that sort of data.

View solution in original post

2 REPLIES 2
Astounding
PROC Star

The safest way is to cut back on the OUTPUT statement:

 

output out=db.ebcollectorsum sum=;

 

The names that follow SUM= go in order.  The first name after SUM= becomes the sum of the first variable in the VAR statement.  Second name after SUM= becomes the sum of the second variable in the VAR statement.  Instead of making sure you have a match all the way through both sets of names, use the OUTPUT statement above.  It tells SAS to re-use the incoming variable names, making them the SUM in the output data set.  That trick only works when you are generating a single statistic (such as SUM only, but not SUM and MEAN at the same time for example).

 

Also note a better strategy ... don't use a DATA step at all.  You already have YEAR in the data set.  It's easy to tell PROC MEANS to compute a separate set of sums for each YEAR:

 

proc means data=db.ebcollector nway;

class year;

var aadt iri tot fi pd;

output out=db.ebcollectorsum sum=;

run;

 

You you will get a separate observation in the output data set for each year.  It's a good idea to learn how to program with that sort of data.

iliyan
Obsidian | Level 7

Well, that was "Astounding', that worked like a charm thank you very much.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 1117 views
  • 0 likes
  • 2 in conversation