Good Morning All,   I have been trying to wrap my brain around this but I can't seem to come up with an acceptable solution.  I am thinking I might have to use a nested select, but I wanted to ask my question here first.  I am using PROC SQL to select data below.  What I want to happen is to select ID and SUM(Count), however I want to limit this data on value = 20.  The issue is that I want the entire ID to be calculated when this happens, not just limited to the value = 20 line.  Appreciate any guidance on this one.     Thank you!   ID Value Count 1 20 1000 1 19 200 1 18 100 1 17 9992 2 20 10000 3 12 1000 4 13 100 5 12 200 5 13 200 5 20 10000 5 21 2000 6 19 200 7 22 300 7 11 400 7 10 2000 ... View more

Hello! My friend came to me with this issue and I wasn't sure how to unlock this one.  I mentioned I would come on SAS communities to see if anyone could help me figure this out.  The issue is he has a list that has numbers listed that when given dropped most of the leading zeros.  I wasn't sure how to pad these within a sysfunc, I do know that the put / input functions work outside of sysfunc, but I read that putc and inputc are needed for macro values.     Thank you! %let clients = '12345'; %let clientsb = %sysfunc(putc(%sysfunc(inputc(&clients,best7.)),z7.)); %put &clientsb; ... View more

So macro programming has always been a weakness for me, but I am trying to do something fairly simple to give it a go for date automation.     So what I want to happen is based on the run date, it will create the Quartercal for me based on my logic below.  So I am missing something when running this macro but I am not sure what it is.  I haven't done many of these if, then, do, else if, types before.   Thank you!   %macro Quarter_SAS(Quartercal); %let Today_SAS = %sysfunc(date(),Date9.); %LET Run_Date = %SYSFUNC(INPUTN(&Today_SAS., Date9.)); %LET Quarter_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., 0., B), Date9.); %if &Quarter_SAS = ('01MAR2020') %then %do; %let Quartercal=('Q12020'); %end; %else %if &Quarter_SAS = ('01AUG2020') %then %do; %let Quartercal=('Q22020'); %end; %else %if &Quarter_SAS = ('01DEC2020') %then %do; %let Quartercal=('Q32020'); %end; %else %if &Quarter_SAS = ('01JAN2021') %then %do; %let Quartercal=('Q42020'); %end; %mend Quarter_SAS; %put &Quarter_SAS; ... View more

So I understand how to set up automatic dates in SAS:   /* Normal Date (today) */ %let Today_SAS = %sysfunc(date(),Date9.); %LET Run_Date = %SYSFUNC(INPUTN(&Today_SAS., Date9.));   /*This will give the date 3 months back */ %LET Three_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -3., B), Date9.); /* This will give the date 6 months back */ %LET Six_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -6., B), Date9.); /* This will give the date 9 months back */ %LET Nine_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -9., B), Date9.); /* This will give the date 12 months back */ %LET Twelve_SAS = %SYSFUNC(INTNX(MONTH, &Run_Date., -12., B), Date9.);     My Question is how do I pass this macro into a Hadoop HIVE passthrough?   Here is a crude example below of the code I am using to pass through to Hadoop.  I am hoping to automate the date process, but I am at a loss on this one.   Proc SQL noerrorstop; Connect to HADOOP (server='xxx' port=xxx); Execute (set mapreduce.job.queuename=sas.xxx) by HADOOP; Execute (set parquet.compression=SNAPPY) by HADOOP; Execute (drop table if exists test.test) by HADOOP; Execute (create table test.test stored as parquet as select distinct a.Element1, a.Element2, a.Element3, a.Element4, b.Element5 from Test.Test2 as a LEFT JOIN Test.Test3 b on a.ElementJoin=b.ElementJoin where to_date(b.Element5) >= ('2018-01-01') ) by hadoop; quit; Thanks for any suggestions or helpful material to reference. ... View more