Problem with Array

[Kc2]

Hello,

I have a macro variable var which contains a list of all the numerical dates in my dataset.

The variable fmt contain the list of  format associated with those variables in var.

The variable newvar is a list of the variables I want to create..

%put var = &var;
var = TRTSDT TRTSDTM TRTEDT TRTEDTM DTHDT DCUTDT ASTDT AENDT
%put fmt = &fmt;
fmt = E8601DA E8601DT E8601DA E8601DT E8601DA E8601DA E8601DA E8601DA
%put newvar = &newvar;
newvar = TRTSDTC TRTSDTMC TRTEDTC TRTEDTMC DTHDTC DCUTDTC ASTDTC AENDTC

I want to create a dataset tat keeps each numerical variable above  but also adds the corresponding character variable (date or date time) based on the format.

I am using an array in a data step but it is not working.

data adae_new;
set adae_old;
array olddt[*] &var.;
array oldfmt[*] &fmt.;
array newdtc[*] &newvar.;
do i = 1 to dim(olddt);
if oldfmt[i]="E8601DA" then newdtc[i] = put(olddt[i], yymmdd10.);
else if oldfmt[i]="E8601DT" then newdtc[i] = put(olddt[i], datetime19.);
drop i;
end;
run;

This code is not working the character variables are all empty.

Any suggestions as to how to loop through the list of numerical dates in a dataset and and convert them into character based on the format used ?

[Tom · Accepted solution]

If you going to put character strings into the new variables then they need to be defined as character.

array newdtc[*] $19 &newvar.;

Do you really have variables named  E8601DA E8601DT etc?

data adae_new;
  set adae_old;
  array olddt[*] &var.;
  array newdtc[*] $19 &newvar.;
  do i = 1 to dim(olddt);
    if scan("&fmt",i,' ')="E8601DA" then newdtc[i] = put(olddt[i], yymmdd10.);
    else newdtc[i] = put(olddt[i], datetime19.);
  end;
  drop i;
run;

[Kc2]

Thank you Tom, it worked.

I do not have  variables  named   E8601DA E8601DT . I created a list of numerical variable and their associated format using a proc sql. I then assigned the format and the variables to a macro variable.

proc sql;
create table ds0 as
select libname, memname, name, type, length, label, format
from dictionary.columns
where libname='WORK' and type='num' and upcase(label) like '%DATE%';

select name into :var separated by ' '
from ds0 ;

select scan(format,1,'.') into :fmt separated by ' '
from ds0 ;

quit;

which gives

var = TRTSDT TRTSDTM TRTEDT TRTEDTM DTHDT DCUTDT ASTDT AENDT
fmt = E8601DA E8601DT E8601DA E8601DT E8601DA E8601DA E8601DA E8601DA.

Is there a way to order the newly created variables dynamically next to their numerical counterpart instead of doing it for each datasets manually ?

[Kc2]

In order  to dynamically order the new character variables next to the numerical ones. I am using the varnum variable.  

I have these macro variables;

var = TRTSDT TRTSDTM TRTEDT TRTEDTM DTHDT DCUTDT ASTDT AENDT
fmt = E8601DA E8601DT E8601DA E8601DT E8601DA E8601DA E8601DA E8601DA
newvar = TRTSDTC TRTSDTMC TRTEDTC TRTEDTMC DTHDTC DCUTDTC ASTDTC AENDTC
pos = 45 46 47 48 53 54 61 67

data adae0;
set adae_copy;
array varnum[*] &pos.;
array olddt[*] &var.;
array newdtc[*] $19 &newvar.;

do i = 1 to dim(olddt);
if scan("&fmt",i,' ')="E8601DA" then newdtc[i] = put(olddt[i], yymmdd10.);
else newdtc[i] = put(olddt[i], datetime19.);
varnum[i] = input(&pos[i],8.)+ 0.5;
end;
drop i;
run;

I get the error messages;

array varnum[*] &pos.;-
                                  79

ERROR 79-322: Expecting a ).

ERROR: The array varnum has been defined with zero elements.
ERROR: Too many array subscripts specified for array varnum.
NOTE: Line generated by the macro variable "POS".
 45 46 47 48 53 54 61 67
--
388
76

ERROR 352-185: The length of numeric variables is 3-8.

Given that the array are by default numeric, I do not understand the issue.

thank you.

[Tom]

You need to figure out what SAS code you want to run before you start trying to use code generation to create the code from data.

The order of the variables in the dataset is determined by the order they appear in the data step.  So you need something that lists the variables in the order you want them at the top of your program. 

Sometimes you can use a RETAIN statement since that will set the variables location in the dataset, but it does not force a particular type/length for the variable.  But be careful that retaining the variable does not cause issues with how you are populating it.  

Or you could generate a LENGTH statement (or ATTRIB statements) that actually defines the variables.

So is the goal to generate a character translations of every DATE or DATETIME variable in the source dataset?  Is the pattern to add the letter C to the end of the existing variable's name?  So the character version of XXX would be in XXXC?

Get the list of variables.  I will use PROC CONTENTS because then the FORMAT variable contains just the name of the format instead of the full format specification that is shown in DICTIONARY.COLUMNS.  

proc contents data=have noprint out=names(keep=libname memname varnum name type length format) ;
run;
proc sort data=names;
  by varnum;
run;

Now you can use that to generate new variable definitions.

data new_names;
  set names;
  length fmtcat $8;
  fmtcat=fmtinfo(format,'cat');
  output;
  if fmtcat in ('date','datetime') then do;
    varnum=varnum+0.5;
    name=cats(name,'C')
    type=2;
    if fmtcat='date' then length=10;
    else length=18;
    output;
  end;
run;  

Now you can use that to generate a LENGTH statement for ALL of the variables, new and old.

filename length temp;
data _null_;
  set new_names end=eof;
  file length;
  if _n_=1 then put 'length';
  put @3 name @;
  if type=2 then put '$' @;
  put length ;
  if eof then put ';';
run;

And a series of assignment statements to create the new "C" variables.  The -L modifier on the datetime format specification is to handle the bug in the DATETIME format.  You should be able to display 9 character date and 8 character time with a colon separator in 18 characters.  But if you use DATETIME18. you only get 2 digit years.  If you use DATETIME19. you get four digit years, but a leading spaces.  So the -L will left align the 18 characters so it moves the space to the end.

filename dates temp;
data _null_;
  set new_names;
  file dates;
  if type=2 and fmtcat in ('date','datetime') then do;
     put name '=put(' name +(-2) ',' @;
     if fmtcat='date' then put 'yymmdd10.' @;
     else put 'datetime19.-L' @;
     put ');';
  end;
run;

Now you can use those two files with the LENGTH statement and the series of assignment statements in a new data step.

data want;
%include length / source2;
  set have;
%include dates / source2;
run;

[Kc2]

Thanks Tom,

I will try out the code you shared.

I have a folder with 20 datasets. I need to loop through each datasets and create a character version of the dates variables.

XXX becomes XXXC

I need the character variables in the datasets to be next to their numeric part XXX XXC.

and have the correct format used when converting from numeric to character: date or datetime.

The macro I coded does everything except the ordering (your suggestion to declare the array as character helped.)

For reviewing purposes we need the numeric variables and the character variables next to each other.

[Kc2]

Hi Tom,

I update my code with your suggestion.

the iterative loop to go through each dataset in the work directory  is instead counting the number of variables

 of each dataset.

Do you know why the  %do %while (&iter.<= &dscnt.); loop is going through the variable and not the datasets. (ADSL, ADAE, ADCM, ADLB,etc..)

I have 10 datasets and each dataset has 8 numeric dates variables that need to have their corresponding character variables created.

%macro loopOverDs(indir=);
%*--- loop through all SAS datasets and add character dates to numerical dates;
proc sql;
create table datelist as
select libname, memname, name, type, length, label, format, varnum
from dictionary.columns
where libname='WORK' and type='num' and upcase(label) like '%DATE%';
quit;

%*--- get number of datasets;
proc sql noprint;
select count (distinct memname)
into :dscnt
from datelist;
quit;

%put datasets= &dscnt.;

%*--- initiate loop;
%let iter=1;

%do %while (&iter.<= &dscnt.);
%*--- getting libref and dataset name for dataset to work on during this iteration;
data _null_;
set datelist (firstobs=&iter. obs=&iter.); *only read 1 record;
call symput("inMember",strip(memname));
run;
%put inMember= &inMember.;

%*--- Subsetting the dataset to keep only vars on interest;
data ds0(rename=(typen=type));
set datelist;
where memname="&inMember.";
format=scan(format,1,'.');
if type="num" then typen=1;
drop type memname;
run;

%*--- getting number of variable and names of character date variable to convert into numeric;
proc sql noprint ;
select count(distinct name )into :varnum
from ds0 ;

select name into :var separated by ' '
from ds0 ;

select scan(format,1,'.') into :fmt separated by ' '
from ds0 ;

select catx("",name,"C") into :newvar separated by ' '
from ds0 ;
quit;

%put varnum = &varnum;
%put var = &var;
%put fmt = &fmt;
%put newvar = &newvar;

/* data new_names;
set ds0;
length fmtcat $8;
fmtcat=fmtinfo(format,'cat');
output;
if fmtcat in ('date','datetime') then do;
varnum=varnum+0.5;
name=cats(name,'C');
type=2;
if fmtcat='date' then length=10;
else length=18;
output;
end;
run;

filename length temp;
data _null_;
set new_names end=eof;
file length;
if _n_=1 then put 'length';
put @3 name @;
if type=2 then put '$' @;
put length ;
if eof then put ';';
run;

filename dates temp;
data _null_;
set new_names;
file dates;
if type=2 and fmtcat in ('date','datetime') then do;
put name '=put(' name +(-2) ',' @;
if fmtcat='date' then put 'yymmdd10.' @;
else put 'datetime19.-L' @;
put ');';
end;
run;
*/

data &inMember._upd;
/* %include length / source2;*/
set &inMember.;
/* %include dates / source2;*/

array olddt[*] &var.;
array newdtc[*] $19 &newvar.;

do i = 1 to dim(olddt);
if scan("&fmt",i,' ')="E8601DA" then newdtc[i] = put(olddt[i], yymmdd10.);
else newdtc[i] = put(olddt[i], datetime19.);
end;
drop i;
run;

%*--- increment the iterator of the loop;
%let iter=%eval(&iter.+1);
%end;

%mend;

%*--- call the macro;
%loopOverDs(indir=work);

[Tom]

If you want to iterate then just have the DO loop iterate instead of using a WHILE() loop and having to increment the counter by hand.

%*--- get dataset list;
proc sql noprint;
select distinct nliteral(memname)
  into :dslist separated by ' '
  from datelist
;
quit;
%let dscnt=&sqlobs;

%*--- loop over datsaets ;
%do iter=1 %to &dscnt;
  %let inMember=%scan(&dslist,&iter,%str( ),q);
  %put &=inMember;

... Code to process one member ...

%end;

[Kc2]

thank you Tom it solve my problem.