SAS Beginer - Issues faced using proc rank to sort data

[Deepak13]

data cars;
proc import datafile= "/folders/myshortcuts/Myfolders/cars12.csv" dbms=csv replace out=cars;
Getnames= Yes;
run;
proc print data= cars;
run;

data cars;
set cars;
format msrp;
msrp=compress(msrp, "$");
run;
proc print data= cars;
run;
proc rank data= cars out= cars1;
var msrp;
ranks msrp_rank;
run;
proc sort data= cars1;
by msrp_rank;
run;
proc print data= cars1;
run;

LOG:
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         data cars;
 
 NOTE: The data set WORK.CARS has 1 observations and 0 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.01 seconds
       cpu time            0.00 seconds
       
 
 74         proc import datafile= "/folders/myshortcuts/Myfolders/cars12.csv" dbms=csv replace out=cars;
 75         Getnames= Yes;
 76         run;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 77          /**********************************************************************
 78          *   PRODUCT:   SAS
 79          *   VERSION:   9.4
 80          *   CREATOR:   External File Interface
 81          *   DATE:      05JUL19
 82          *   DESC:      Generated SAS Datastep Code
 83          *   TEMPLATE SOURCE:  (None Specified.)
 84          ***********************************************************************/
 85             data WORK.CARS    ;
 86             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 87             infile '/folders/myshortcuts/Myfolders/cars12.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
 88                informat Make $5. ;
 89                informat Model $30. ;
 90                informat Type $6. ;
 91                informat Origin $6. ;
 92                informat DriveTrain $5. ;
 93                informat MSRP $9. ;
 94                informat Invoice $9. ;
 95                informat EngineSize best32. ;
 96                informat Cylinders best32. ;
 97                informat Horsepower best32. ;
 98                informat MPG_City best32. ;
 99                informat MPG_Highway best32. ;
 100               informat Weight best32. ;
 101               informat Wheelbase best32. ;
 102               informat Length best32. ;
 103               format Make $5. ;
 104               format Model $30. ;
 105               format Type $6. ;
 106               format Origin $6. ;
 107               format DriveTrain $5. ;
 108               format MSRP $9. ;
 109               format Invoice $9. ;
 110               format EngineSize best12. ;
 111               format Cylinders best12. ;
 112               format Horsepower best12. ;
 113               format MPG_City best12. ;
 114               format MPG_Highway best12. ;
 115               format Weight best12. ;
 116               format Wheelbase best12. ;
 117               format Length best12. ;
 118            input
 119                        Make  $
 120                        Model  $
 121                        Type  $
 122                        Origin  $
 123                        DriveTrain  $
 124                        MSRP  $
 125                        Invoice  $
 126                        EngineSize
 127                        Cylinders
 128                        Horsepower
 129                        MPG_City
 130                        MPG_Highway
 131                        Weight
 132                        Wheelbase
 133                        Length
 134            ;
 135            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 136            run;
 
 NOTE: The infile '/folders/myshortcuts/Myfolders/cars12.csv' is:
       Filename=/folders/myshortcuts/Myfolders/cars12.csv,
       Owner Name=root,Group Name=vboxsf,
       Access Permission=-rwxrwx---,
       Last Modified=22Jun2019:12:55:06,
       File Size (bytes)=38142
 
 NOTE: 428 records were read from the infile '/folders/myshortcuts/Myfolders/cars12.csv'.
       The minimum record length was 68.
       The maximum record length was 111.
 NOTE: The data set WORK.CARS has 428 observations and 15 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.01 seconds
       cpu time            0.01 seconds
       
 
 428 rows created in WORK.CARS from /folders/myshortcuts/Myfolders/cars12.csv.
   
   
   
 NOTE: WORK.CARS data set was successfully created.
 NOTE: The data set WORK.CARS has 428 observations and 15 variables.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.16 seconds
       cpu time            0.11 seconds
       
 
 137        proc print data= cars;
 138        run;
 
 NOTE: There were 428 observations read from the data set WORK.CARS.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           2.70 seconds
       cpu time            2.69 seconds
       
 
 139        
 140        data cars;
 141        set cars;
 142        format msrp;
 143        msrp=compress(msrp, "$");
 144        run;
 
 NOTE: There were 428 observations read from the data set WORK.CARS.
 NOTE: The data set WORK.CARS has 428 observations and 15 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 145        proc print data= cars;
 146        run;
 
 NOTE: There were 428 observations read from the data set WORK.CARS.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           2.76 seconds
       cpu time            2.73 seconds
       
 
 147        proc rank data= cars out= cars1;
 148        var msrp;
 ERROR: Variable MSRP in list does not match type prescribed for this list.
 149        ranks msrp_rank;
 150        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.CARS1 may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
 WARNING: Data set WORK.CARS1 was not replaced because this step was stopped.
 NOTE: PROCEDURE RANK used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 
 151        proc sort data= cars1;
 152        by msrp_rank;
 ERROR: Variable MSRP_RANK not found.
 153        run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 
 154        proc print data= cars1;
 155        run;
 
 NOTE: There were 428 observations read from the data set WORK.CARS1.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           2.66 seconds
       cpu time            2.66 seconds
       
 
 156        
 157        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 170        

Hi,

In the above SAS program I have tried to sort the data by "MSRP" using proc rank function, but the machine is not considering "msrp" as a variable. Kindly make use of the above SAS code and attached source file.

Support is very much appreciated!

Kind regards,

Deepak.

[andreas_lds]

All variables in the var-statement must be numeric, unfortunately proc import reads the variable as text. I would start by getting rid of proc import and use a data step to read the csv files. That way you have 100% control of what is read and how it is written to the dataset.

data work.cars;
   length 
      Make $ 5 
      Model $ 30
      Type $ 6
      Origin $ 6
      DriveTrain $ 5
      MSRP  8
      Invoice EngineSize Cylinders Horsepower
      MPG_City MPG_Highway Weight Wheelbase Length 8
   ;

   informat MSRP Invoice comma32.;
   format MSRP Invoice dollar10.;

   infile "/folders/myshortcuts/Myfolders/cars12.csv" delimiter="," dsd firstobs=2;

   input Make Model Type Origin DriveTrain MSRP Invoice EngineSize Cylinders Horsepower
      MPG_City MPG_Highway Weight Wheelbase Length;

run;

[Reeza]

*this line does nothing;

data cars;

*imports data;
proc import datafile= "/folders/myshortcuts/Myfolders/cars12.csv" dbms=csv replace out=cars;
Getnames= Yes;
run;

*prints to display;
proc print data= cars;
run;

*removes $ sign from msrp - does not convert to numeric;
*using the same name is a bad idea here;
data cars2;
set cars;
format msrp;
msrp=compress(msrp, "$");
msrp_num = input(msrp, comma.);
format msrp_num dollar20.;
run;


*displays new data set;
proc print data= cars2;
run;

*create groups;
proc rank data= cars2 out= cars3 ;
var msrp_num;
ranks msrp_rank;
run;

*sort and run;
proc sort data= cars3;
by msrp_rank;
run;
proc print data= cars3;
run;

[Reeza]

Fixed a bad variable reference - created the numeric variable but didn't use it later on.

[Deepak13]

Hi,

Apologies, Iam still in learning curve. Still Iam not clear why we have to use dollar to rank MSRP?

[Reeza · Accepted solution]

SAS has two types of data, numeric and character. It read in the MSRP as a character variable due to the $ and comma and it likely had quotes as well - assuming it did import correctly of course.

You cannot do summary statistics on character variables, because the mean of a bunch of letters doesn't make sense. So you need to convert it to a number. When you're doing thatyou need to tell SAS what format it current is in, which is comma (since you removed the dollar sign). If it had the dollar sign, I would have used a DOLLAR informat instead.

[Deepak13]

Many thanks for your prompt support Reeza! Your help is very much appreciated!