what would be the best way for ClaimID defined as char or numer in different dataset

[JenniferK]

Hi, I have the scenario that I have to use retain statement for claimID variable. When some data have ClaimID as numeric value and some have as character value, can I create a macro that passes through with checking the ClaimID type?

I need to create something like %if CLAIMID = numeric value %then %do;

data work.createclaimID;

%if CLAIMID = numeric value %then %do;

   length INI_ClaimID 8. ;

   set sortedsrc;

    by MemID

   retain INI_;

   if first.MemID then do;

       INI_ClaimID   = .;

   end;

%end;

%if CLAIMID = character value %then %do;

   length INI_ClaimID $60. ;

   set sortedsrc;

    by MemID

   retain INI_;

   if first.MemID then do;

       INI_ClaimID   = ‘ ‘;

   end;

%end;

[JenniferK · Accepted solution]

Here is the solution if anybody needs it.

/** sample data set **/
data sortedsrc;
claimid=123;
memid=1;
run;

data sortedsrc2;
claimid='123';
memid=1;
run;

%macro lst(dsn);
/** open dataset **/
%let dsid=%sysfunc(open(&dsn));

/** retrieve the type of the variable claimid and place in macro variable &typ **/
%let x=%sysfunc(varnum(&dsid,claimid));
%let typ=%sysfunc(vartype(&dsid,&x));

/** close dataset **/
%let rc=%sysfunc(close(&dsid));

data final_&dsn;
set &dsn;
by MemID;
retain INI_:;

%if &typ = C %then %do;
length INI_ClaimID $60. ;
if first.MemID then do;
INI_ClaimID = ' ';
end;
%end;
%else %do;
length INI_ClaimID 8. ;
if first.MemID then do;
INI_ClaimID = .;
end;
%end;
run;

%mend lst;
%lst(sortedsrc);
%lst(sortedsrc2);

[Reeza]

Congrats on finding the answer yourself 🙂

One suggestion for your code, to make it more general, you can use CALL MISSING which applies to both numeric/character variables instead of assigning missing values. Length statements usually need to be before the SET statement for variables that already exist as well but if it worked for you I guess you're fine. 

data final_&dsn;

%if &typ = C %then 
      length INI_ClaimID $60. ;
%else 
     length INI_ClaimID 8. ;


set &dsn;
by MemID;
retain INI_:;

if first.MemID then call missing(INI_ClaimID);

run;