topic proc transpose with by variable as numeric in SAS Programming

proc transpose with by variable as numeric

GeorgeBonanza — Tue, 15 Mar 2022 13:20:50 GMT

Hello,

I have a dataset that I am importing that has sales by ID by year where each year is a column name. I want to transpose the data but I want YEAR variable to be numeric but it looks like PROC TRANSPOSE creates it as character. Is there a way to force it to be numeric within the PROC TRANSPOSE? I did a quick search but couldn't find anything.

Here is some sample data and the PROC TRANSPOSE that I am using. It is the _NAME_ variable (renamed to YEAR) that I want to be numeric.

options validvarname=any;
data HAVE;
	input ID $ "2018"n "2019"n "2020"n;
	datalines;
001 1000 1500 1250
002 2000 500 1800
;
run;

proc transpose
	data= HAVE
	out=  WANT (rename= (_NAME_ = YEAR COL1 = SALES));
	by ID;
	var _numeric_;
quit;

Thanks in advance.

Re: proc transpose with by variable as numeric

Kurt_Bremser — Tue, 15 Mar 2022 13:33:40 GMT

A variable name is by definition always character, and TRANSPOSE does not test all your variable names to see if all of them are (to the human eye) numeric. For this, you always need a second step:

proc transpose
  data=have
  out=long (rename=(COL1 = SALES))
;
by ID;
var _numeric_;
run; /* PROC TRANSPOSE needs a RUN, not a QUIT! */

data want;
set long;
year = input(_name_,4.);
drop _name_;
run;

Re: proc transpose with by variable as numeric

GeorgeBonanza — Tue, 15 Mar 2022 13:52:49 GMT

Thanks Kurt for the quick response. You've forced me to ask another question, however. I know data step uses RUN but how do I know whether to use RUN or QUIT with a PROC? I want my code to be correct so as to not look amateurish.

Re: proc transpose with by variable as numeric

Kurt_Bremser — Tue, 15 Mar 2022 14:04:09 GMT

There are only a few procedures who need the QUIT, as they support RUN-group processing. Think DATASETS or CATALOG. But these are few and far between 😉

In addition to that, SQL is a procedure that executes each statement immediately and also needs a QUIT to terminate.

If in doubt about a certain procedure, look up its documentation to see if it supports RUN-group processing.