SAS Notes

Loading dataset ‘cars’

/* Define a temporary filename */
filename tempurl TEMP;

/* Assign the URL to the temporary fileref */
proc http
    url="https://support.sas.com/documentation/onlinedoc/viya/exampledatasets/cars.csv"
    method="get"
    out=tempurl;
run;

/* Import the data from the URL into a SAS dataset */
data work.cars;
    infile tempurl dlm =',' firstobs=2 MISSOVER; /* Set delimiter and skip header row */
    input Make $ Model $ Type $ Origin $ DriveTrain $ MSRP Invoice 
    EngineSize Cylinders Horsepower MPG_City MPG_Highway Weight 
    Wheelbase Length
    
    ;
run;

/* Display the imported dataset */
proc print data=work.cars;
run;

Select columns contain string ‘M’ in their names

We already have data cars

filename tmp temp; 
data _null_; 
  file tmp; 
  if 0 then set cars; 
  length varname $32; 
  do until (varname='varname'); 
    call vnext(varname); 
    if index(varname,'M') then put varname; 
  end; 
run; 

data want; 
   set cars; 
   keep 
     %include tmp; 
   ; 
run; 

The output dataset then has columns such as ‘Make’, ‘Model’, ‘MGP_city’…

Select columns contain ‘M’ using ‘PROC SQL’

/* Select columns containing 'M' */
proc sql;
    select name into :col_list separated by ' ' 
    from dictionary.columns
    where libname = 'WORK' and memname = 'CARS' and name contains 'M';
quit;

/* Display selected columns */
proc print data=cars (keep=&col_list);
run;

Select columns not contain ‘M’

proc sql;
    select name into :col_list separated by ' ' 
    from dictionary.columns
    where libname = 'WORK' and memname = 'CARS' and not (name contains 'M');
quit;

/* Display selected columns */
proc print data=cars (keep=&col_list);
run;

The output dataset has columns not start with ‘M’ such as ‘Type’, ‘Origin’, ‘Invoice’

proc sql

proc sql;   
    SELECT Make, AVG(Invoice) AS AVG_Invoice 
    FROM cars
    WHERE Origin = 'Asia'
    GROUP BY Make 
    ;
quit;

The output serults is the summary means of Invoice by Make.

Notice: some SQL functions will be incompatible with some versions of SAS, Ex:PERCENTILE_CONT().

Strings

creat dataset for Acura cars

data Acura_data;
set cars;
where Make = 'Acura';
run;

creat column with first name only from full name column

data want;
  set have;
  firstname = substr(name, 1, find(name, ' ') - 1);
run;

proc print data=want;
run;

creat column with last name only from full name column

data want;
  set have;
  firstname = substr(name, 1, find(name, ' ') + 1);
run;

proc print data=want;
run;

creat column with first 4 characters of name

SUBSTR(string, start, length)

data want;
  set have;
  firstname = substr(name, 1, 4;
run;

proc print data=want;
run;

creat column with last 4 characters of name

SUBSTR(string, start, length)

data want;
  set have;
  firstname = substr(name, length(name)-3, 4);
run;

proc print data=want;
run;

creat column with IF clause

Name like ‘John’ is ‘pass’

data want;
  set have;
  if substr(name, 1,1) = 'J' then newvar = 'pass';
  else newvar = 'fail';
run;

proc print data=want;
run;

replace string

data want;
    set have; 
    new_var = tranwrd(old_var, 'old_string', 'new_string');
run;