topic Re: date in character and numeric format in SAS Programming

date in character and numeric format

Aayushi_17 — Thu, 21 Nov 2019 11:37:41 GMT

Hi,

i have many raw data sets having date under same variable name in character and numeric format. How to set dates by changing all dates into character format without changing separately in each raw datasets

For eg;

in libraw.have has date in character format

in libraw.have1 has date in numeric format

while setting

data want;

set have have1;

run;

am getting error.since there are many datasets not able to find and chnage all numeric into character fomrat

similarly i have many datasets

Thanks in advance.

Re: date in character and numeric format

Jagadishkatam — Thu, 21 Nov 2019 11:59:02 GMT

You can try to rename the date variables in each dataset and convert the date from numeric to character in the datasets with numeric variable with the same name, then if you set the datasets you will not get any error.

Re: date in character and numeric format

Kurt_Bremser — Thu, 21 Nov 2019 12:10:58 GMT

Correct your import process, so that you get consistent structure in your datasets. Fixing it after the fact is unnecessary extra work.

Re: date in character and numeric format

PaigeMiller — Thu, 21 Nov 2019 12:52:21 GMT

I agree with @Kurt_Bremser , who presents the best solution. But sometimes us data analysts don't really control the process, the input data is in Excel and we have no choice here but to use the Excel file. If that's the case (and this has not been stated by @Aayushi_17) then you would most likely need to write a macro to loop over all the SAS data sets and make the necessary changes.

Re: date in character and numeric format

Tom — Thu, 21 Nov 2019 14:22:30 GMT

You can use the RENAME= dataset option when reading the dataset to rename to numeric variables to a different name. Then you can convert them into character in the step that combines, rather than modifying each dataset before combining them.

data want;
  set have have1(rename=(date=date_num)) ;
  if missing(date) and not missing(date_num)) then date=put(date_num,date9.);
run;

You can query the metadata on your source datasets to determine which ones need the rename= option added.

Re: date in character and numeric format

ballardw — Thu, 21 Nov 2019 18:06:14 GMT

This bit of code may help. It will list all dataset names (actually members so if you have VIEWS they will appear as well) in the library LIBRAW that have a variable named "DATE". The result will have the data set name as row header and columns labeled 'char' or 'num' for character and numeric with a 1 indicating which type the variable is in that set.

Proc tabulate data=sashelp.vcolumn;
   where libname='LIBRAW' and upcase(name)='DATE';
   class memname type;
   tables memname,
          type*n=' '
          ;
run;

I would usually suggest that any "date" be an actual SAS date numeric value with an appropriate date format applied.

If you have a good back up of the data, maybe copy the entire library for back up you might consider using some code similar to this:

data _null_;
   set sashelp.vcolumn;
   where libname='LIBRAW' and upcase(name)='DATE' and type='char';
   length str $ 45;
   str = catx('.',libname,memname);
   call execute('data '|| str||';');
   call execute('set '|| str||' (rename=(date=chardate));');
   /* put your favorite standard date format below*/
   call execute('   date=input(chardate,anydtdte.);
   format date yymmdd10.;
   drop chardate;
   run;');
run;

Which barring misfortune would make new copies of all of the data sets with the date as a numeric value formatted using the yymmdd10. format (or which ever format you replace in the code.

CALL execute places text in to an execution queue and the argument to the function is a string value that evaluates to appropriate code.