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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: