Hi guys,   I have a question. I want to pull out distinct customer_id and certain (expiration) date from a table that contains history of expiration dates for certain bonuses.    PROC SQL; create table work.datt as select distinct t1.CONT_ID, max(t2.EXPIRE_DATE) FORMAT=DATETIME20. from CONTRACTS t1 inner join CONTRACT_HIST t2 on t2.CONT_ID = t1.CONT_ID; QUIT;   But then it takes one, general max date and pulls out contracts only for this one, specific date. Same happens when I'm using function 'having date =max(date)'.   Could you please help out how to make it max(date) but separate for each customer? thanks! ... View more

Hi guys,   I'm trying to change the date from DATETIME to date (with datepart) and then extract only month and year by changing format to MONYY7. I want to do so in order to count certain ID for each month. The issue is, SAS treats this date as previous dates, but it gives me the month, in example while using distinct I will get two separate lines with MAY 2016 and again MAY 2016 because days might differ. How to avoid that, to sum things up?   please help!   PROC SQL; CREATE TABLE WORK.QUERY AS SELECT DISTINCT datepart(t1.EXPIRE_DATE,9) FORMAT=MONYY7., t1.CONT_ID FROM CONTRACT t1 GROUP BY t1.EXPIRE_DATE; QUIT; ... View more

Hi guys,   I'm trying to use both INTCK and DATEPART function, but it gives me an error: ERROR 22-322: Syntax error, expecting one of the following: a name, *.     This is my code:   PROC SQL; CREATE TABLE Client_month AS SELECT DISTINCT t1.ID, INTCK('month', today(), datepart(t1.client_datetime, DATE9.)) as MONTHS FROM Customers t1 QUIT;   I'm trying to obtain a range in months between today() date and a date, that is in datetime format, therefore I'm trying to use datepart format date9 to get it in normal date format.   I can't figure it out. Could you please help?   thanks!   ... View more

Hi guys,   I'd like to create a table with a datetime column and then insert some random dates. For example:   proc sql; create table PALETA (DATA_ID DATETIME); insert into paleta (DATA_ID) values ('1Sep2015:0:0:0'dt); insert into paleta (DATA_ID) values ('1Oct2015:0:0:0'dt); insert into paleta (DATA_ID) values ('1Nov2015:0:0:0'dt); insert into paleta (DATA_ID) values ('1Dec2015:0:0:0'dt); insert into paleta (DATA_ID) values ('1Jan2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Feb2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Mar2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Apr2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1May2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Jun2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Jul2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Aug2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Sep2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Oct2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Nov2016:0:0:0'dt); insert into paleta (DATA_ID) values ('1Dec2016:0:0:0'dt); quit;   The issue is, I receive asterisk result instead of a datetime. In order to make it work, I need to create another procedure:   proc sql; create table Paleta2 as SELECT DATA_ID FORMAT=DATETIME20. from paleta; quit;   Can you please help out how to make it in one procedure instead of adding the second one? thanks! ... View more