Identify elements in two columns

daradanye — Thu, 07 Mar 2019 20:52:14 GMT

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!

Re: Identify elements in two columns

ballardw — Thu, 07 Mar 2019 22:02:43 GMT

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;

topic Identify elements in two columns in SAS Programming

Identify elements in two columns

Re: Identify elements in two columns