topic Adding average column and dropping columns in SAS Programming

Adding average column and dropping columns

Steelersgirl — Wed, 28 Nov 2018 14:48:19 GMT

I am reading in a data set with the album name and song times in min and sec for each song. I then used an array to find the song time in just minutes for each song. I then want to add a variable for the mean song length of every album and drop the min and sec variables. I have been trying the following code but it is not creating the new mean song length variable nor dropping the other variables.

DATA songs;
FILENAME webpage URL 'http://people.stat.sc.edu/Hitchcock/beatles_songlengths.txt';
INFILE webpage ;
INPUT album_name  $ 1-38 min1 sec1 min2 sec2 min3 sec3 min4 sec4 min5 sec5 min6 sec6 min7 sec7 min8 sec8 min9 sec9 min10 sec10 min11 sec11 min12 sec12 min13 sec13 min14 sec14;
ARRAY min(14) min1 min2 min3 min4 min5 min6 min7 min8 min9 min10 min11 min12 min13 min14;
ARRAY sec(14) sec1 sec2 sec3 sec4 sec5 sec6 sec7 sec8 sec9 sec10 sec11 sec12 sec13 sec14;
ARRAY songtime(14);
DO i=1 to 14;
songtime(i)=min(i)+sec(i)/60;
END;
RETAIN avgsong;
avgsong=songtime(i)/i;
RUN;
  
  
PROC PRINT DATA=songs;
DROP min(i) sec(i) i;
RUN;

Re: Adding average column and dropping columns

RW9 — Wed, 28 Nov 2018 15:27:40 GMT

First off, please avoid coding in upper case, it really makes reading code harder. Updated below, note do not use min() as that is a SAS function. I would always - good programming practice - put arrays in curly braces so as to discern them from functions:

filename webpage url 'http://people.stat.sc.edu/Hitchcock/beatles_songlengths.txt';

data songs (keep=album_name avgsong);
  infile webpage;
  retain avgsong;
  input album_name  $ 1-38 min1 sec1 min2 sec2 min3 sec3 min4 sec4 min5 sec5 min6 sec6 min7 sec7 min8 sec8 min9 sec9 min10 sec10 min11 sec11 min12 sec12 min13 sec13 min14 sec14;
  array m{14};
  array s{14};
  array songtime{14};
  do i=1 to 14;
    songtime{i}=m{i}*60+s{i};
  end;
  avgsong=mean(of songtime:);
run;

Also note, you can simplify your code, as it is always min, then sec, by using a two level array:

 
filename webpage url 'http://people.stat.sc.edu/Hitchcock/beatles_songlengths.txt';

data songs;
  infile webpage;
  retain avgsong;
  input album_name  $ 1-38 min1 sec1 min2 sec2 min3 sec3 min4 sec4 min5 sec5 min6 sec6 min7 sec7 min8 sec8 min9 sec9 min10 sec10 min11 sec11 min12 sec12 min13 sec13 min14 sec14;
  array time{14,2} min1--sec14;
  array songtime{14};
  do i=1 to 14;
    songtime{i}=time{i,1}*60+time{i,2};
  end;
  avgsong=mean(of songtime:);
run;

Re: Adding average column and dropping columns

ballardw — Wed, 28 Nov 2018 15:37:49 GMT

instead of

avgsong=songtime(i)/I;

try

avgsong=mean( of songtime(*)); IF you want the average song length per album (record).

If you want the average song length across all albums you then need to do something else such as Proc Means/Summary.

You again to forget to post the ERRORS your code generates. You would get an Array subscript out of range for the line

avgsong=songtime(i)/I;

because AFTER the loop the index variable I will have a value of 15.

If you want to drop a variable put the DROP in the data step, not proc print. That is why you got a message similar to:

NOTE: The DROP and KEEP statements are not supported in procedure steps in this release of the
      SAS System. Therefore, these statements are ignored.

in the log for proc print. HINT: Read the log. In the data step

drop min: sec: i;

If you do not want to print the dropped variable(s) either explicitly list the variables you want to print using a VAR statement or use the data set option as

PROC PRINT DATA=songs (DROP= min: sec: I);

DROP statements or options will not recognize an array reference and will have an error in the log. The variable list shortcut min: can reference all variables that start with MIN or a range list like drop min1-min14; min(i) is right out.