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!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.