BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
daradanye
Obsidian | Level 7

Hi,

 

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

File_typeFile_date
mm|cd|ab2000-01-01|2000-04-03|2000-02-28
fg|mm2010-04-11|2010-05-03
…………
j10|ab|mm2013-11-01|2013-12-03|2013-12-25
mm2014-05-01

 

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

File_typeFile_datefile_type_newfile_date_new
mm|cd|ab2000-01-01|2000-04-03|2000-02-28mm2000-01-01
fg|mm2010-04-11|2010-05-03mm2010-05-03
……………………
j10|ab|mm2013-11-01|2013-12-03|2013-12-25mm2013-12-25
mm2014-05-01mm

2014-05-01

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
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;

 

View solution in original post

1 REPLY 1
ballardw
Super User
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;

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 659 views
  • 1 like
  • 2 in conversation