Hi @Tom
Amazing support and comprehensive answer
I tested the code successfully outside the macro.
I just want to conclude a little bit:
I did not get the data from R programmer, it is from Excel (I just check the original files).
I agree with all your recent two posts, now the code in total is
libname test "C:\Users\pnguyen\Desktop\merge2";
data _null_;
if 0 then set test.argentina_merge2;
call symputx('bdate_type',vtype(bdate));
stop;
run;
data haa_;
set test.argentina_merge2;
%if &bdate_type=C %then %do;
if length(bdate)=5 and upcase(bdate) ne 'NA' then
SASDATE = input(BDATE,32.)+'30DEC1899'd ;
else SASDATE= input(BDATE,anydtdte.);
%end;
%else %do;
else SASDATE=BDATE;
%end;
format SASDATE yymmdd10.;
if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE)));
run;
Can I ask a further question?
That when I put it into a macro, it turns out an errors regarding the code "if not missing"
The code is
data _null_;
if 0 then set &outfm.merge2;
call symputx('bdate_type',vtype(bdate));
stop;
run;
data &outfm.merge2_(drop=NAME);
set &outfm.merge2;
%if &bdate_type=C %then %do;
if (length(bdate)=5) and (upcase(bdate) ne 'NA') then
SASDATE = input(BDATE,32.)+'30DEC1899'd ;
else SASDATE= input(BDATE,anydtdte.);
%end;
%else %do;
else SASDATE=BDATE;
%end;
format SASDATE yymmdd10.;
if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE)));
run;
The error log is
134 +
data ARGENTINA_merge2_(drop=NAME); set ARGENTINA_merge2; else SASDATE=BDATE; format SASDATE yymmdd10.; if not
____
_________
160
484
134 !+missing(sasdate)
ERROR 160-185: No matching IF-THEN clause.
NOTE 484-185: Format $YYMMDD was not found or could not be loaded.
135 + then fir_age = log(1+(2020-year(SASDATE))); run;
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
135:34
88 The SAS System 23:21 Saturday, February 13, 2021
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ARGENTINA_MERGE2_ may be incomplete. When this step was stopped there were 0 observations and 70
variables.
WARNING: Data set WORK.ARGENTINA_MERGE2_ was not replaced because this step was stopped.
My whole code is
options compress=yes reuse=yes;
options mergenoby=error;
OPTIONS MPRINT;
%macro ImportAndTranspose(
File=
, cur=
, outf=
, StartSheet=
, EndSheet=
);
%local i;
%do i = &StartSheet. %to &EndSheet.;
%if &i=1 %then %do;
proc import datafile= "&File."
out= &outf.&i.
dbms= xlsx
replace;
range= "Sheet1$A:X";
getnames= yes;
run;
proc sort data= &outf.&i.;
by Type;
run;
%end;
%else %if &i=34 %then %do;
proc import datafile= "&File."
out= &outf.&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= &outf.&i.;
by Type;
run;
proc transpose data= &outf.&i.
out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
data &outf.&i._outx;
set &outf.&i._out;
if s&i. in: ('NA', '$$', '') then s&i. = " ";/********/
run;
%end;
%else %if (&i ne 1) and (&i ne 34) %then %do;
proc import datafile= "&File."
out= &outf.&i.
dbms= xlsx
replace;
range= "Sheet&i.$A:AG";
getnames= yes;
run;
proc sort data= &outf.&i.;
by Type;
run;
proc transpose data= &outf.&i.
out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
by Type;
VAR '1988'N - '2019'N;
run;
data &outf.&i._outx;
set &outf.&i._out;
if input(s&i., ?? 32.) = . then s&i.2=.;/*added line 21_Jan*/
else s&i.2=input(s&i., 32.);/*added line 21_Jan*/
/*if s&i. in: ('NA', '$$', '..') then s&i.2=.;
else if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.);*/
drop s&i.;
rename s&i.2=s&i.;
run;
%end;
%end;
/* Merging*/
%let outfm = %substr(&outf,1,%length(&outf)-5);
data &outfm.merge1;
merge
%do i =&StartSheet.+1 %to &EndSheet.;
&outf.&i._outx(keep= type year s&i.)
%end;
;
by type year;
run;
data &outfm.merge2;
merge
&outf.&StartSheet.
&outfm.merge1
;
by type;
run;
data _null_;
if 0 then set &outfm.merge2;
call symputx('bdate_type',vtype(bdate));
stop;
run;
data &outfm.merge2_(drop=NAME);
set &outfm.merge2;
%if &bdate_type=C %then %do;
if (length(bdate)=5) and (upcase(bdate) ne 'NA') then
SASDATE = input(BDATE,32.)+'30DEC1899'd ;
else SASDATE= input(BDATE,anydtdte.);
%end;
%else %do;
else SASDATE=BDATE;
%end;
format SASDATE yymmdd10.;
if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE)));
run;
*********************************************;
/* Delete file results to reduce operating*/
*************;
%local i;
%do i = &StartSheet.+1 %to &EndSheet.;
*+1 because sheet 1 behave differently*;
proc delete data=&outf.&i.;
proc delete data=&outf.&i._out;
proc delete data=&outf.&i._outx;
%end;
*****************;
proc delete data=&outf.1;
proc delete data=&outfm.merge1;
**********************************************;
************************************************;
**EXPORT DATASET**;
/*https://communities.sas.com/t5/SAS-Programming/making-a-sas7bdat-file/td-p/417487*/
libname myfolder 'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Cfiltering';
proc copy in=work out=myfolder;
select &outfm.merge2_;
run;
quit;
/*proc delete data=&outfm.filter;*/
%mend;
/*Replicate all files in one folder*/
filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
fname = scan(dread(did,i),1,'.');
length short_fn $29 currency $3 ;
short_fn= cats(substr(fname, 1,length(fname)-3),'_');
currency=substr(fname,length(fname)-2);
cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),
',cur=',currency,
',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');
call execute(cmd);
end;
keep fname;
run;
Warmest regards.
Thank you for your help, have a fabulous and productive day! I am a novice today, but someday when I accumulate enough knowledge, I can help others in my capacity.
Click image to register for webinar
