/*
 _______           _        _______  _______
(  ____ )|\     /|( \      (       )(       )
| (    )|| )   ( || (      | () () || () () |
| (____)|| |   | || |      | || || || || || |
|  _____)( (   ) )| |      | |(_)| || |(_)| |
| (       \ \_/ / | |      | |   | || |   | |
| )        \   /  | (____/\| )   ( || )   ( |
|/          \_/   (_______/|/     \||/     \|

 ______   _______ _________ _______
(  __  \ (  ___  )\__   __/(  ___  )
| (  \  )| (   ) |   ) (   | (   ) |
| |   ) || (___) |   | |   | (___) |
| |   | ||  ___  |   | |   |  ___  |
| |   ) || (   ) |   | |   | (   ) |
| (__/  )| )   ( |   | |   | )   ( |
(______/ |/     \|   )_(   |/     \|

_________ _______  _______  _______  _______ _________
\__   __/(       )(  ____ )(  ___  )(  ____ )\__   __/
   ) (   | () () || (    )|| (   ) || (    )|   ) (
   | |   | || || || (____)|| |   | || (____)|   | |
   | |   | |(_)| ||  _____)| |   | ||     __)   | |
   | |   | |   | || (      | |   | || (\ (      | |
___) (___| )   ( || )      | (___) || ) \ \__   | |
\_______/|/     \||/       (_______)|/   \__/   )_(

*/

Options Symbolgen;
/*  Declare temporary library   */
%global tmplib;
%let tmplib=work;

/*  Macro for initializing empty datasets   */
%macro init(dset=);
proc sql;
    drop table &dset.;
quit;

data &dset.;
length Indicator $20
    Reporting_country $4
    reporting_country_label $40
    'EU / WB'n $3
    Week_Starting 8
    YEAR 8
    Month 8
    Week 8
    nat_orig $10
    Nationality_pers $10
    nat_pers_label $40
    Returning_country $10
    returning_country_label $40
    Ctry_return $10
    Failed_Asy $4
    Criminal_Sanction $4
    FacSus $4
    ProtReason $20
    Bus 8
    Van 8
    Car 8
    Airplane 8
    Other 8
    NA 8
    Region $40
    Total 8
    Forced 8
    Voluntary 8
    Stolen 8
    Lost 8
    PassengerType $10
    BorderSection $10
    'Entry/Exit'n $6
    Return_operation $16
    file $40
;
Indicator="QQQQQQQQQQQQQQQQQQQ";
Reporting_country="QQQQ";
reporting_country_label="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
'EU / WB'n="QQQ";
nat_orig="QQQQ";
Nationality_pers="QQQQQQQQQQ";
nat_pers_label="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
Returning_country="QQQQQQQQQQ";
returning_country_label="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
Ctry_return="QQQQQQQQQQ";
Failed_Asy="QQQQ";
Criminal_Sanction="QQQQ";
FacSUS="QQQQ";
ProtReason="QQQQQQQQQQQQQQQQQQQQ";
Region="QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ";
PassengerType="QQQQQQQQQQ";
BorderSection="QQQQQQQQQQ";
'Entry/Exit'n="QQQQQQ";
Return_operation="QQQQQQQQQQQQQQQQ";
file="QQQQQQQQQQQQQQQQQQQQ";

format Indicator $20.
    Reporting_country $20.
    reporting_country_label $40.
    'EU / WB'n $3.
    Week_Starting DATE9.
    YEAR bestd5.
    Month bestd5.
    Week bestd5.
    nat_orig $4.
    Nationality_pers $4.
    nat_pers_label $40.
    Returning_country $4.
    returning_country_label $40.
    Ctry_return $10.
    Failed_Asy $4.
    Criminal_Sanction $4.
    FacSus $4.
    ProtReason $20.
    Bus bestd5.
    Van bestd5.
    Car bestd5.
    Airplane bestd5.
    Other bestd5.
    NA bestd5.
    Region $40.
    Total bestd14.
    Forced bestd14.
    Voluntary bestd14.
    Stolen bestd14.
    Lost bestd14.
    PassengerType $10.
    BorderSection $10.
    'Entry/Exit'n $6.
    Return_operation $16.
    file $40.
;
run;
%mend init;

proc sql noprint;
    drop table &tmplib..PVLMM_Data;
quit;

%init(dset=&tmplib..PVLMM_Data);

/*  Insert basic information    */
%macro basic_input(dset=);
    proc sql noprint;
        /*  Country ISOCode */
        select country
        into :ctry
        from &tmplib..ctry;
    quit;
    /*  Insert data */
    data &dset.;
    set &dset.;
        length country $4 file $40;
        Reporting_country="&ctry";
        file="&file";
    run;

%mend basic_input;
/*  Normalize nationalities */
%macro norm_nat(in=);
    data &in.(rename=(nat2=nat_orig));
    set &in.;
        nat2=strip(upcase(nat_orig));
        drop nat_orig;
    run;
%mend norm_nat;
/*  Macro for reading Excel tabs    */
%macro Import_One(file=);
    %put File for import: &file.;

    proc sql noprint;
        drop table &tmplib..ctry;
        drop table &tmplib..ASY1;
        drop table &tmplib..RET1_1;
        drop table &tmplib..RET1_2;
        drop table &tmplib..IBC1AOut;
        drop table &tmplib..Passport;
        drop table &tmplib..PassFlow;
    quit;

    data &tmplib..ctry;
        length country $4 file $40;
        file="&file";
        country=scan(file,1,"-");
        /*
        year=scan(file,3,"-")*1;
        */
        week_start=scan(file,4,"-")*1;
        week_end=scan(scan(file,5,"-"),1,".")*1;
    run;

    /*  Year    */
    /*
    proc import out=&tmplib..year
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="Instr & Def$C26:C26";
        usedate=yes;
        scantime=yes;
    run;

    proc contents data=&tmplib..year out=&tmplib..year2(keep=name) noprint;
    run;

    proc sql noprint;
    select name
    into :yrtmp
    from &tmplib..year2;
    quit;

    %global year;
    %let year=&yrtmp;*/
    /*  Asylum Inland   */
    proc import out=&tmplib..ASY1
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="1 Asylum Inland$A8:K300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Return  */
    proc import out=&tmplib..RET1_1
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="2 Return$A8:I300";
        usedate=yes;
        scantime=yes;
    run;

    proc import out=&tmplib..RET1_2
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="1 Return$A8:I300";
        usedate=yes;
        scantime=yes;
    run;

    /*  1A Out BCP  */
    proc import out=&tmplib..IBC1AOUT
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="1A Out BCP$A8:F300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Passport    */
    proc import out=&tmplib..PASSPORT
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="3 Passport$A8:F300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Passenger flow  */
    proc import out=&tmplib..PASSFLOW
            datafile="&root.\&file" replace
            dbms=excelcs;
        range="2 Passenger flow$A8:E300";
        usedate=yes;
        scantime=yes;
    run;

    /*  Check if tables exist   */
    proc sql noprint;
        select memname
        into :test1
        from sashelp.vmember
        where strip(upcase(memname))="ASY1";

        select memname
        into :test2
        from sashelp.vmember
        where strip(upcase(memname))="RET1_1";

        select memname
        into :test3
        from sashelp.vmember
        where strip(upcase(memname))="RET1_2";

        select memname
        into :test4
        from sashelp.vmember
        where strip(upcase(memname))="IBC1AOUT";

        select memname
        into :test5
        from sashelp.vmember
        where strip(upcase(memname))="PASSPORT";

        select memname
        into :test6
        from sashelp.vmember
        where strip(upcase(memname))="PASSFLOW";
    quit;
    /*  If Asylum tab exists then proceed   */
    %put &test1.;
    %if %symexist(test1) %then %do;
        data &tmplib..ASY2;
        set &tmplib..ASY1;
            where Totals*1 ne 0 and Totals*1 ne .;
            rename Nationality=nat_orig
                'Suspected Facilitation'n=FacSus
                'Stated Reason for Protection'n=ProtReas;
            Total=Totals*1;
            drop Totals Bus Van Car Airplane Other 'N.A.'n;
            length Indicator $20;
            Indicator="Asylum Inland";
            Bus1=Bus*1;
            Van1=Van*1;
            Car1=Car*1;
            Airplane1=Airplane*1;
            Other1=Other*1;
            NA='N.A.'n*1;
        run;

        data &tmplib..ASY2;
        set &tmplib..ASY2;
        rename Bus1=Bus Van1=Van Car1=Car Airplane1=Airplane Other1=Other;
        run;

        %basic_input(dset=&tmplib..ASY2);
        %norm_nat(in=&tmplib..ASY2);
    %end;
    /*  If Return tab exists then proceed   */
    %put &test2.;
    %if %symexist(test2) %then %do;
        data &tmplib..RET2_1;
        set &tmplib..RET1_1;
            where 'Total Returns'n*1 ne 0 and 'Total Returns'n*1 ne .;
            rename Nationality=nat_orig
                'Country of Return'n=Ctry_return
                'Returning Country'n=returning_country_label
                'Negative Asylum _Decision'n=Failed_Asy
                'Criminal Sanctions /_Extradition'n=Criminal_Sanction;
            Total='Total Returns'n*1;
            Forced='Total Forced'n*1;
            Voluntary='Total Voluntary'n*1;
            length Indicator $20;
            if F9 eq "refusal" then Indicator="Refusal";
            else Indicator="Return MS";
            drop 'Total Returns'n 'Total Forced'n 'Total Voluntary'n F9;
        run;

        %basic_input(dset=&tmplib..RET2_1);
        %norm_nat(in=&tmplib..RET2_1);

        data &tmplib..RET2F(drop=Total Voluntary) &tmplib..RET2V(drop=Total Forced);
        set &tmplib..RET2_1;
            where Indicator ne "Refusal";
        run;

        data &tmplib..RET2R(drop=Voluntary Forced)
        set &tmplib..RET2_1;
            where Indicator eq "Refusal" and Total ne 0;
        run;

        data &tmplib..RET2F;
        set &tmplib..RET2F;
            Return_operation="Forced";
            where Forced ne 0;
            rename Forced=Total;
        run;

        data &tmplib..RET2V;
        set &tmplib..RET2V;
            Return_operation="Voluntary";
            where Voluntary ne 0;
            rename Voluntary=Total;
        run;

        %init(dset=&tmplib..RET2);

        data &tmplib..RET2;
            if 0 then modify &tmplib..RET2;
            set &tmplib..RET2F open=defer;
            output;
        run;

        data &tmplib..RET2;
            if 0 then modify &tmplib..RET2;
            set &tmplib..RET2V open=defer;
            output;
        run;

        data &tmplib..RET2;
            if 0 then modify &tmplib..RET2;
            set &tmplib..RET2R open=defer;
            output;
        run;

        data &tmplib..RET2;
        set &tmplib..RET2;
            where Total ne . and Total ne 0;
        run;
        /*
        proc append base=&tmplib..RET2 data=&tmplib..RET2F;
        run;

        proc append base=&tmplib..RET2 data=&tmplib..RET2V;
        run;

        proc append base=&tmplib..RET2 data=&tmplib..RET2R;
        run;
        */
    %end;

    %put &test3.;
    %if %symexist(test3) %then %do;
        data &tmplib..RET2;
        set &tmplib..RET1_2;
            where 'Total Returns'n*1 ne 0 and 'Total Returns'n*1 ne .;
            rename Nationality=nat_orig
                'Country of Return'n=Ctry_return
                'Returning Country'n=returning_country_label
                'Negative Asylum _Decision'n=Failed_Asy
                'Criminal Sanctions /_Extradition'n=Criminal_Sanction;
            Total='Total Returns'n*1;
            Forced='Total Forced'n*1;
            Voluntary='Total Voluntary'n*1;
            length Indicator $20;
            if F9="refusal" then Indicator="Refusal";
            else Indicator="Return MS";
            drop 'Total Returns'n 'Total Forced'n 'Total Voluntary'n F9;
        run;

        %basic_input(dset=&tmplib..RET2);
        %norm_nat(in=&tmplib..RET2);
    %end;
    /* If 1A Out BCP tab exists then proceed */
    %put &test4.;
    %if %symexist(test4) %then %do;
        data &tmplib..IBC1AOut2;
        set &tmplib..IBC1AOut;
            where Totals*1 ne 0 and Totals*1 ne .;
            rename Nationality=nat_orig;
            Total=Totals*1;
            length Indicator $20;
            Indicator="ibc-1a";
            keep Week_Starting Nationality nat_orig Total Indicator;
        run;

        %basic_input(dset=&tmplib..IBC1AOut2);
        %norm_nat(in=&tmplib..IBC1AOut2);
    %end;
    /*  If Passport tab exists then proceed */
    %put &test5.;
    %if %symexist(test5) %then %do;
        data &tmplib..Passport2;
        set &tmplib..Passport;
            length Indicator $20;
            rename Nationality=nat_orig 'Administrative region'n=Region;
            Total='Total new requests'n*1;
            Lostn=lost*1;
            Stolenn=stolen*1;
            Indicator="Passport";
            where Week_Starting ne .;
            keep Week_Starting Nationality nat_orig Total Indicator 'Administrative region'n Region Lostn Stolenn;
        run;

        data &tmplib..Passport3;
        set &tmplib..Passport2;
            where (Total ne 0
            or Total ne .
            or Lostn ne 0
            or Lostn ne .
            or Stolenn ne 0
            or Stolenn ne .) and
            Week_Starting ne .;
            rename Lostn=Lost Stolenn=Stolen;
        run;

        %basic_input(dset=&tmplib..Passport3);
        %norm_nat(in=&tmplib..Passport3);
    %end;
    /*  If PassFlow tab exists then proceed */
    %put &test6.;
    %if %symexist(test6) %then %do;
        data &tmplib..PASSFLOW2;
        set &tmplib..PASSFLOW;
            length Indicator $20;
            where 'Totals'n*1 ne 0 and 'Totals'n*1 ne .;
            rename 'Type of passangers'n=PassengerType 'Border section'n=BorderSection;
            Total=Totals*1;
            Indicator="Passenger flow";
            keep Indicator Week_Starting PassengerType Total BorderSection 'Entry/Exit'n;
        run;

        %basic_input(dset=&tmplib..PassFlow2);
    %end;
    /*  Append tables   */
    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..ASY2 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..RET2 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..IBC1AOut2 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..Passport3 open=defer;
        output;
    run;

    data &tmplib..PVLMM_Data;
        if 0 then modify &tmplib..PVLMM_Data;
        set &tmplib..PassFlow2 open=defer;
        output;
    run;
%mend Import_One;

proc sql;
    drop table &tmplib..PVLMM_Data3;
    drop table &tmplib..PVLMM_Data4;
    drop table &tmplib..Base_countries;
    drop table &tmplib..PVLMM_Data5;
    drop table &tmplib..nat_dictionary;
    drop table &tmplib..PVLMM_Data6;
    drop table &tmplib..nat_labels;
    drop table &tmplib..PVLMM_Data7;
    drop table &tmplib..PVLMM_Data_Final;
    drop table &tmplib..PVLMM_Data_Final2;
    drop table &tmplib..PVLMM_Data_Final3;
quit;

/*  Loop for importing all existing files in a folder   */
%macro ImportAllFiles;
    /*  Path to main folder */
    %global path1;
    %let path1 = \\aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\REPORTING\TOOLS\PVLMM\&year.;
    %global root;
    %let root=&path1.\&path2;
    %put Chosen path: &root.;

    filename exdir "&root.";
    %let dirid = %sysfunc(DOPEN(exdir));
    filename exdir clear;
    %put &dirid.;
    %let dircnt = %sysfunc(DNUM(&dirid));
    %put &dircnt.;
    /*  Loop over all files in chosen folder    */
    %do i = 1 %to &dircnt;
        %let dirread = %sysfunc(DREAD(&dirid,&i));
        /*  Process only WBMM files */
        %local regexid;
        %let regexid1=%sysfunc(prxmatch("WBMM","&dirread"));
        %let regexid2=%sysfunc(prxmatch("PVLMM","&dirread"));
        %if &regexid1>0 %then %do;
        %put Current file:  &dirread.;
            /*  Macro for reading excel tabs    */
            %Import_One(file=&dirread);
        %end;
        %if &regexid2>0 %then %do;
        %put Current file:  &dirread.;
            /*  Macro for reading excel tabs    */
            %Import_One(file=&dirread);
        %end;
    %end;
    %let rc =%sysfunc(DCLOSE(&dirid));
    %put &rc.;

%mend ImportAllFiles;

/********************************************/
/*  Choose year !!!!!!!!!!!!!!!!!!!!!!!!!!  */
/********************************************/
%global year;
%let year=2014;
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
/*  Change the folder name here !!!!!!!!!   */
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
%global path2;
%let path2=7 April 2014;
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/

%global path1;
%let path1 = \\aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\REPORTING\TOOLS\PVLMM\&year.;
%global root;
%let root=&path1.\&path2;

*%Import_One(file=ALB-WBMM-2013-49-52-v1.xls);

%ImportAllFiles;

/*  All post-import operations  */

proc sql;
    create table &tmplib..PVLMM_Data3 as
    select distinct Indicator,Reporting_country,reporting_country_label,'EU / WB'n,Week_Starting,YEAR,Month,Week,nat_orig,Nationality_pers,nat_pers_label,Returning_country,returning_country_label,Ctry_return,Failed_Asy,Criminal_Sanction,FacSus,ProtReason,sum(Bus) as Bus,sum (Van) as Van,sum(Car) as Car,sum(Airplane) as Airplane,sum(Other) as Other,sum(NA) as NA,Region,sum(Total) as Total,Stolen,Lost,PassengerType,BorderSection,'Entry/Exit'n,Return_operation,file
    from &tmplib..PVLMM_Data
    group by Indicator,Reporting_country,reporting_country_label,'EU / WB'n,Week_Starting,YEAR,Month,Week,nat_orig,Nationality_pers,nat_pers_label,Returning_country,returning_country_label,Ctry_return,Failed_Asy,Criminal_Sanction,FacSus,ProtReason,Region,Stolen,Lost,PassengerType,BorderSection,'Entry/Exit'n,Return_operation,file;
quit;

data &tmplib..PVLMM_Data4;
set &tmplib..PVLMM_Data3;
    where Indicator ne "QQQQQQQQQQQQQQQQQQQ";
    if Reporting_country="GER" then Reporting_country="DEU";
    if Reporting_country="GER" then Reporting_country_label="Germany";
    if Reporting_country="DEU" then Reporting_country_label="Germany";
    if Nationality_pers="" and Indicator ne "" then do;
        Nationality_pers="UNK";
        nat_pers_label="Unknown";
    end;
run;

/********************************************/
/*  Import Isocode dictionary - BruteForce  */
/********************************************/

%put ________Import Isocode dictionary________;

%put ________Import Isocode dictionary Finished________;

proc sql;
create table &tmplib..PVLMM_Data5 as
select * from &tmplib..PVLMM_Data4
left outer join base_countries
on PVLMM_Data4.Reporting_country=base_countries.iso3code;
quit;

/*  Create Nationality_pers based upon Nat_orig - check for errors  */
/*  Path to Excel with list of corrected nationality iso codes  */

%global path;
%let path=\\aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\FRAN\REPORTING\TOOLS\PVLMM;
%global in_file;
%let in_file=RANCompileMacro_afterJuly2013.xlsx;

proc import out=&tmplib..nat_dictionary(rename=(Coor=nat_orig UNK=nat_dest)) replace
            datafile="&path.\&in_file"
            dbms=excelcs;
        range="Corr_code$A2:B800";
        usedate=yes;
        scantime=yes;
run;

libname shredstg '\\aofrontex.local\sas-shareddrive\SASData';

proc sql noprint;
    create table &tmplib..PVLMM_Data6 as
    select * from &tmplib..PVLMM_Data5 as t1
    left outer join &tmplib..nat_dictionary as t2
    on t1.nat_orig=t2.nat_orig;

    create table &tmplib..nat_labels as
    select distinct nationality_pers, nat_pers_label as temp
    from shredstg.fran_analysis;
quit;

data &tmplib..PVLMM_Data7;
set &tmplib..PVLMM_Data6;
    reporting_country_label=name;
    nationality_pers=nat_dest;
    if eu=1 or schengen=1 then do;
        'EU / WB'n='MS';
    end;
    else do;
        'EU / WB'n='WB';
        if Indicator='Return MS' then Indicator='Return WB';
    end;
    drop id name capname frename capfrename iso2code iso3code eu regionid subregionid visa regionname subregionname schengen nat_dest;
run;

proc sql noprint;
    create table &tmplib..PVLMM_Data_Final as
    select * from &tmplib..PVLMM_Data7 as t1
    left outer join &tmplib..nat_labels as t2
    on t1.nationality_pers=t2.nationality_pers;

    create table &tmplib..PVLMM_Data_Final2 as
    select * from &tmplib..PVLMM_Data_Final
    left outer join base_countries
    on PVLMM_Data_Final.returning_country_label=base_countries.name;
quit;

data &tmplib..PVLMM_Data_Final3;
set &tmplib..PVLMM_Data_Final2;
    format Week_Starting date9.;
    returning_country=iso3code;
    nat_pers_label=temp;

    drop temp drop id name capname frename capfrename iso2code iso3code eu regionid subregionid visa regionname subregionname schengen;
    Week=week(Week_Starting,'v');
    if week(Week_Starting,'v')<10 then do;
        YYYYWK=strip(strip(year(Week_Starting))||'Wk '||strip(week(Week_Starting,'v')));
    end;
    else do;
        YYYYWK=strip(strip(year(Week_Starting))||'Wk'||strip(week(Week_Starting,'v')));
    end;
    if month(Week_Starting)<10 then do;
        YYYYMM=strip(strip(year(Week_Starting))||'M '||strip(month(Week_Starting)));
    end;
    else do;
        YYYYMM=strip(strip(year(Week_Starting))||'M'||strip(month(Week_Starting)));
    end;
    Month=month(Week_Starting);
    Year=year(Week_Starting);

    if month(Week_Starting)<4 then YYYYQ=cats(Year,'Q1');
    if month(Week_Starting)>=4 and Month<7 then YYYYQ=cats(Year,'Q2');
    if month(Week_Starting)>=7 and Month<10 then YYYYQ=cats(Year,'Q3');
    if month(Week_Starting)>=10 then YYYYQ=cats(Year,'Q4');

    where Total ne 0 and Total ne .;

    if month=12 and week=1 then YYYYWK=strip(strip(year(Week_Starting)+1)||'Wk'||strip(week(Week_Starting,'v')));
run;
/*
proc sort data=&tmplib..PVLMM_Data_Final3 out=&tmplib..PVLMM_Data_Final4 noduprecs;
by _ALL_;
run;
*/
/*  Update the table in Sample Shared Storage   */
/*
data _null_;
    call symdel('tab_exist');
run;

proc sql noprint;
        select memname
        into :tab_exist
        from sashelp.vmember
        where memname="PVLMM_TRIAL_2" and libname="SHREDSTG";
quit;

%macro condition;
%if %symexist(tab_exist) %then %do;
    data shredstg.PVLMM_TRIAL_2;
        if 0 then modify shredstg.PVLMM_TRIAL_2;
        set &tmplib..PVLMM_Data_Final3 open=defer;
        output;
    run;
%end;
%else %do;
    data shredstg.PVLMM_TRIAL_2;
        set &tmplib..PVLMM_Data_Final3;
    run;
%end;
%mend condition;

*%condition;*/

    data shredstg.PVLMM;
        if 0 then modify shredstg.PVLMM;
        set &tmplib..PVLMM_Data_Final3 open=defer;
        output;
    run;

/*
proc append base=shredstg.PVLMM data=&tmplib..PVLMM_Data_Final3;
run;
/*
proc sort data=shredstg.PVLMM_TRIAL_2 noduprecs;
by _ALL_;
run;
*/