topic Re: proc transpose numeric suffix issue? in SAS Procedures

proc transpose numeric suffix issue?

ybz12003 — Wed, 21 Jun 2017 14:13:45 GMT

Hello:

I have transpose program below. However, I got an error message for Log shown below. Please advice how to fix this question.

proc transpose data=have out=trans

prefix=Sale_record;

var Sale1_recode-Sale6_record;

by CarID;

run;

quit;

ERROR: Sale1_record does not have a numeric suffix.

Re: proc transpose numeric suffix issue?

collinelliot — Wed, 21 Jun 2017 14:16:29 GMT

The "-" to list variables only applies when the variables have a numeric suffix: sales1- sales10.

You can use a "--" to list variables that are actually in that order in the data, but be sure of what you're asking it to do.

Re: proc transpose numeric suffix issue?

Rwon — Wed, 21 Jun 2017 15:17:51 GMT

To add on to Collin,

You will have to list out all the variables in the var statement since they don't end in a number. Alternatively, you can rename the variables to end with a number if you want to use the '-' feature.

Assuming that 'Sale1_recode' was a typo for 'Sale1_record':

*Method 1;
proc transpose data=have out=trans
var Sale1_record Sale2_record Sale3_record Sale4_record Sale5_record Sale6_record;
by CarID;
run;
quit;

*Method 2;
proc transpose data=have(rename=(Sale1_record=Sale_record1 Sale2_record=Sale_record2 Sale3_record=Sale_record3 
                                 Sale4_record=Sale_record4 Sale5_record=Sale_record5 Sale6_record=Sale_record6)) 
                         out=trans
prefix=Sale_record;
var Sale_record1-Sale_record6;
by CarID;
run;
quit;

Re: proc transpose numeric suffix issue?

ybz12003 — Thu, 22 Jun 2017 11:47:51 GMT

Hi, Rwon:

Thanks for your information. unfortunately, I have 50 similar names as Sale(n)_record. I can't list all of them one by one.

Re: proc transpose numeric suffix issue?

Rwon — Thu, 22 Jun 2017 14:06:31 GMT

If the variables in the dataset are structured such that each set of names are in order (left to right), you can use '--' for each set of variables. This will take all variables in the dataset between and including the ones listed.

I.e.

proc transpose data=have out=trans
var Sale1_record--Sale6_record;
by CarID;
run;
quit;

Re: proc transpose numeric suffix issue?

Tom — Thu, 22 Jun 2017 14:17:34 GMT

In the future make sure to name series of variablees with the sequence number at the end of the name.

Personally for a one off analysis I would just run a little program to generate the names and then copy and paste them.

data _null_;
  do i=1 to 50 ;
    name = cats('sales',i,'_record');
    put name ;
  end;
run;

If you find that you need to reference a number of different lists of variables in this way then you might want to make a little macro to generate the names.

%macro names(n,prefix,suffix);
%local i;
%do i=1 %to &n ; &prefix.&i.&suffix %end;
%mend;
...
var %names(50,sales,_record);

Re: proc transpose numeric suffix issue?

ybz12003 — Thu, 22 Jun 2017 14:18:58 GMT

Could I use dim to replace 50?

Re: proc transpose numeric suffix issue?

Tom — Thu, 22 Jun 2017 14:40:14 GMT

DIM() of what?

@ybz12003 wrote:

Could I use dim to replace 50?