If you want to collapse observations of the same BY value into a single observation, you can use the sample code in this SAS note.   http://support.sas.com/kb/24/745.html       ... View more

This is an approach to put all observations of the same Deptid value into separate data sets named from the Datadescription variable.   　 data have; infile datalines truncover; input @1 deptid 1. deptdescription $ & 3-20; datalines; 1 Maths 2 Astronomy 1 Maths 3 Physics 4 Public Health 3 Physics ; run;   /* Sort by Deptid so first./last. logic can be used in the next step */ proc sort data=have; by deptid; run;   /* CALL SYMPUTX creates macro variables &Subj1, &Subj2, etc from the values of Deptdescription.*/ /* The total unique values of Deptdescription are stored in &Total                             */ data _null_; set have; by deptid; if first.deptid then do; i+1; call symputx('subj'|| left(put(i,2.)),deptdescription); end; if last.deptid then call symputx('total',i); run;   /* Within a macro %DO loop the data sets are created and those observations with the same */ /* Deptdescription values are read with a WHERE clause                                    */ %macro test; %do i=1 %to &total; data &&subj&i; set have; where deptdescription="&&subj&i"; run; %end; %mend test;   /* invoke the macro */ %test   ... View more

Editor's note: this topic is very popular.  Thanks to @peterz, @Hima, and @AskoLötjönen and others who contributed useful replies.  We have consolidated some of these into this single reply so that future readers may benefit.     /* input data */ data input_table; infile cards missover dsd; input ID Year Value1 Value2 Value3; datalines; 1,1999,,270 1,1999,,,350 1,1999,,, 1,2000,20 1,2000,,300 1,2000,,,320 1,2001,,122 1,2001,,, 1,2001,,,500 ; run;　   /*   Method #1 using PROC MEANS  */ proc means data=input_table noprint nway; class id year; var value1 value2 value3; output out=output_table(drop=_type_ _freq_) max=; quit; 　 /* Method #2 using the UPDATE statement */ data want; update input_table (obs=0) input_table; by id year; run;   /* Method 3 using PROC SQL */ proc sql; create table test2 as select id, year, max(value1) as value1, max(value2) as value2, max(value3) as value3 from input_table group by id, year; quit;   /* Resulting data set */   Obs   ID   Year  value1 value2 value3 1         1    1999     .         270     350 2         1     2000  20        300     320 3         1     2001    .         122     500   ... View more

Since the original question was posted in 2011, it's assumed that by now SAS®9 has been installed, therefore this solution is written for customers using SAS®9 technology. .   data a_;   x='abëd34Ý90$#$%a';   new=compress(x,"ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890`~!@#$%^&*()-_=+\|[]{};:',.<>?/ " , "kis"); run; The COMPRESS function is typically used to remove unwanted characters from a variable, but in this example, the characters to keep are specified.   In the second argument of the COMPRESS function, specify characters that you want to keep in X, and specify in the third argument any modifiers. In this example, the modifiers used are:         'k' keeps the characters in the list instead of removing them.       'i'  ignores the case of the characters to be kept or removed.       's' adds space characters (blank, horizontal tab, vertical tab, carriage return, line feed, and form feed) to the list of characters.   Although there are different ways to solve this problem, I chose this approach for simplicity.     ... View more

Editor's Note: This is a popular topic.  The following SAS Note shows how to reorder variables in a SAS data set: Usage Note 8395 ... View more

Editor’s Note: The Enterprise Guide(EG) Import Tool and PROC IMPORT use 2 different types of guessing algorithms behind the scenes when reading a delimited file. If you compare the 2 SAS logs you will see a difference in the code as well as the options on the INFILE statement. Therefore variables may not be typed the same way when using the EG Import Tool versus PROC IMPORT when using in EG.   ... View more