Identify elements in two columns

[daradanye]

Hi,

I have a problem when I use SAS to clean a downloaded dataset. The dataset looks like as below:

File_type	File_date
mm|cd|ab	2000-01-01|2000-04-03|2000-02-28
fg|mm	2010-04-11|2010-05-03
……	……
j10|ab|mm	2013-11-01|2013-12-03|2013-12-25
mm	2014-05-01

Now I want to extract file_type "mm" and its corresponding file_date in the new columns as shown below:

File_type	File_date	file_type_new	file_date_new
mm|cd|ab	2000-01-01|2000-04-03|2000-02-28	mm	2000-01-01
fg|mm	2010-04-11|2010-05-03	mm	2010-05-03
……	……	……	……
j10|ab|mm	2013-11-01|2013-12-03|2013-12-25	mm	2013-12-25
mm	2014-05-01	mm	2014-05-01

I will appreciate it very much if someone can help me with this.  Thanks!

[ballardw · Accepted solution]

data have;
   infile datalines truncover;
   informat file_type $20. file_date $60.;
   input File_type File_date ;
datalines;
mm|cd|ab 2000-01-01|2000-04-03|2000-02-28 
fg|mm    2010-04-11|2010-05-03 
run;

data want;
   set have;
   file_type_new = 'mm';
   file_new_date= scan(file_date,findw(strip(file_type),'mm','|','e'),'|');

run;

If you want to get all of the filetypes and dates on a separate record something like:

data want;
   set have;
   do i = 1 to countw(file_type,'|');
      file_type_new = strip(scan(file_type,i,'|'));
      file_new_date= scan(file_date,i,'|');
      output;
   end;
   drop i;
run;

if any of your file types have more than 2 characters you need to provide a LENGHT statement for File_type_new to set that length.

I personally would change file_new_date to a SAS date value with :

data want;
   set have;
   do i = 1 to countw(file_type,'|');
      file_type_new = strip(scan(file_type,i,'|'));
      file_new_date= input( scan(file_date,i,'|'),yymmdd10.);
      output;
   end;
   drop i;
   format file_new_date yymmdd10.;
run;