proc transpose numeric suffix issue?

[ybz12003]

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.

[collinelliot · Accepted solution]

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.

[Rwon]

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;

[ybz12003]

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. 

[Rwon]

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;

[Tom]

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

[ybz12003]

Could I use dim to replace 50?

[Tom]

DIM() of what?

---

@ybz12003 wrote:

Could I use dim to replace 50?

---