Tag: proc sort

SAS: Concatenate Values in Multiple Rows by Group

For example, data table contains student grades  for the term. Each grade is one record and the term, program, student id information are the same  for the student.  The following data step can be used to order the data table and then concatenate the grade records into something like ‘A/A/B/B+/A’ for each student.

proc sort data= one;
by groupvar1 groupvar2 groupvar3;
run;
data two;
set one;
by groupvar1 groupvar2 groupvar3;
firstgroup = first.groupvar2;
lastgroup = last.groupvar2;
retain catvar;
if firstgroup =1 then catvar=trim(targetvar);
else catvar=catx('/',catvar, targetvar);
if lastgroup ne 1 then delete;
keep groupvar1 groupvar2 catvar;
run;

SAS: Identfy and Remove Duplicate Records

Step 1: Check duplicate records by one field or a combination of fields.

proc freq data=temp noprint;
table var1 /out =dupl (keep =var1 count where = (count >1));
run;

proc freq data=temp noprint;
table var1*var2 /out =dupl (keep =var1 var2 count where = (count >1));
run;

Examine the output to see what circumstances can cause duplicate records.

Step 2: Remove duplicate records.

proc sort data=temp NODUPKEY;
by var1 ;
run;

proc sort data=temp NODUPKEY;
by var1 var2 ;
run;

Alternative method to output unique records and duplicated records in two separate datasets.

proc sort data =temp out=temp1;
by var1 var2;
run;
data unique dup;
set temp1;
by var1 var2;
if first.var2 and last.var2 then output unique;
else output dup;
run;

 

SAS: data extraction and merge routine (1)

/*select data from raw data set*/

data x;

set y;

where var1 = ‘xxx’ and var2 = ‘yyy’;

run;

/*check frequency on variables of interest*/

proc freq;

tables var1 var2;

run;

/*merge to get additional variables needed for analysis*/

proc sort data = x;

by var3;

proc sort data =z;

by var3;

data xz;

merge x (in =a) z;

by var3;

if a;

run;