The script below is not giving the expected result. Please correct the script   /*%let cie=b2;*/ %let cie=b2; data class; format company $1.; set sashelp.class; if &cie eq b1 then company="A"; else if &cie eq b2 then company ="B"; run; ... View more

/* 1. Define LIBNAMEs for your folders */ LIBNAME source spde '/Temp/Alain/OP/dim/'; LIBNAME target spde '/Temp/Alain/OP/dim/old2/'; /* 2. Use PROC DATASETS to move the dataset */ PROC DATASETS LIBRARY=source; COPY IN=source OUT=target MOVE; /* MOVE copies and deletes original */ SELECT dim_calendrier_v1 jourouvrableprovds jourouvrablenatds jourouvrableetdprovds jourouvrableetdnatds; /* Specify the dataset to move */ QUIT; RUN; /* 3. Verify the move (optional) */ proc contents data=source._all_ nods; run; /* Check if it's gone from source */ proc contents data=target._all_ nods; run; /* Check if it's there in target */ How to modify this script if all the tables has the same password (write=po alter=po) Please provide a working example ... View more

I need to run my SAS code two time to get the macro variable value of &cyear. How to solve that issue ?   Please note that the complete fname look like:  fname=pilote_2022_2025_validation_v001.mdf.0.0.0.spds9   %let fname=pilote_????_????_validation_v001.*.spds9; %let path2=/dwh_operation/.../pilot/&fname.; filename oscmd pipe "ls &path2. -t | head -n1 2>&1"; data _null_; infile oscmd; input; a= _infile_; call symputx("a", a); %let cyear=%sysfunc(substr(&a.,65,4)); run; %put &=cyear;   ... View more

Hello,   Below, you will see an example of a SQL pass through connection to a snowflake database and it works very well.   %macro test(value); %LET cn_SNOW = database=&databaseName. SQL_FUNCTIONS=ALL SERVER="&serverName." SCHEMA=&schema. AUTHDOMAIN=&authdomain.; /************************* Defining some Time travel macro variables ******************/ %let current_day=%sysfunc(today(), yymmdd10.); %let now = %sysevalf(%sysfunc(time()) + 120); %let current_time=%sysfunc(putn(&now., time.)); %put &=current_time &=current_day; %let my_date_formatted = %sysfunc(intnx(day, %sysfunc(today()), -7), yymmdd10.); %put &=my_date_formatted; %let timestamp=at(timestamp=> %str(%')&my_date_formatted &current_time%str(%')::timestamp_ltz); %put &=timestamp; proc sql; connect to snow(&cn_SNOW.); CREATE TABLE TEMP AS select * from connection to snow ( WITH TEMPTBL as ( select LKPPGM.*, 'O' as Affinity_Ind from CONTRACTPL_DM.DIM_PL_CNTRCT_VER &timestamp. as contractVer join CONTRACTPL_DM.LKP_PROGRAM &timestamp. as LKPPGM ON LKPPGM.DIM_PL_CNTRCT_VER_KEY = contractVer.DIM_PL_CNTRCT_VER_KEY where LKPPGM.PROG_NM_CD='AFP' and LKPPGM.PROG_CAT_CD = 'INSBUSTRM' order by contractVer.DIM_PL_CNTRCT_VER_KEY ) select * from TEMPTBL ); disconnect from snow; quit; %mend test; %test(be); But if I want to use a case statement, how do we pass the &value. in a Snowflake SQL script? %macro test(value); %LET cn_SNOW = database=&databaseName. SQL_FUNCTIONS=ALL SERVER="&serverName." SCHEMA=&schema. AUTHDOMAIN=&authdomain.; /************************* Defining some Time travel macro variables ******************/ %let current_day=%sysfunc(today(), yymmdd10.); %let now = %sysevalf(%sysfunc(time()) + 120); %let current_time=%sysfunc(putn(&now., time.)); %put &=current_time &=current_day; %let my_date_formatted = %sysfunc(intnx(day, %sysfunc(today()), -7), yymmdd10.); %put &=my_date_formatted; %let timestamp=at(timestamp=> %str(%')&my_date_formatted &current_time%str(%')::timestamp_ltz); %put &=timestamp; proc sql; connect to snow(&cn_SNOW.); CREATE TABLE TEMP AS select * from connection to snow ( WITH TEMPTBL as ( select LKPPGM.*, 'O' as Affinity_Ind from CONTRACTPL_DM.DIM_PL_CNTRCT_VER &timestamp. as contractVer join CONTRACTPL_DM.LKP_PROGRAM &timestamp. as LKPPGM ON LKPPGM.DIM_PL_CNTRCT_VER_KEY = contractVer.DIM_PL_CNTRCT_VER_KEY where LKPPGM.PROG_NM_CD='AFP' and LKPPGM.PROG_CAT_CD = 'INSBUSTRM' order by contractVer.DIM_PL_CNTRCT_VER_KEY ) select * , case when &value. = 'be' then '010' when &value. = 'gc' then '001' End as newvar from TEMPTBL ); disconnect from snow; quit; %mend test; %test(be); I did some test and I know that the &value. is throwing an error. How to solve that issue.     ... View more