topic Re: Knowing if a data was update in a range of dates in SAS Programming

Knowing if a data was update in a range of dates

Jose7 — Thu, 13 May 2021 01:46:26 GMT

Hello Experts,

I want to know the best way to establish if "info1" was updated between a range of dates and gets the dates that was updated in that line of time. Example of the table:

(Day 1) (Day2) (Day3) (Day4) ................

01jan2021 02jan2021 03jan2021 04Jan2021

info1 info1 info1 info1

For example: if "info1" changed in 03jan2021 and also in 04Jan2021, i want to obtain those dates and so on.

Thank so much for your help!

Re: Knowing if a data was update in a range of dates

blueskyxyz — Thu, 13 May 2021 02:53:53 GMT

/*read dataset*/
proc import datafile='C:\Users\bsde0670\Desktop\test.xlsx'
	out= have replace;
	sheet='sheet1';
	getnames=yes;
run;

/*transpose1*/
proc transpose data=have out=trans;
	var day1-day4;
run;

/* if a data was update in a range of dates*/
data want;
	set trans;
	if '01JAN2021'd <= col1 <=	'03JAN2021'd ;
run;

/*transpose2*/
proc transpose data=want out=want1;
	var col1;
run;

Re: Knowing if a data was update in a range of dates

blueskyxyz — Thu, 13 May 2021 02:55:10 GMT

plesase upload your sample dataset to test the code easliy

Re: Knowing if a data was update in a range of dates

Jose7 — Thu, 13 May 2021 16:25:26 GMT

I attached the example, what i want in this case is to obtain de date when the account change the value of the product. (Mark in yellow)

Thks Wen

Re: Knowing if a data was update in a range of dates

blueskyxyz — Fri, 14 May 2021 03:16:26 GMT

what's your filtering? which observation you want to keep?

Re: Knowing if a data was update in a range of dates

Jose7 — Fri, 14 May 2021 15:11:05 GMT

My filter is "ACCOUNT" and I want to keep the date where the account changes the value of product

Re: Knowing if a data was update in a range of dates

Jose7 — Fri, 14 May 2021 18:24:54 GMT

My Friend, I found a solution using loop, Thks Anyway!
%macro sqlloop(start,end);
PROC SQL;
%let DATE_FROM = '05MAY2019'd;
%let DATE_PLUS1 = '06MAY2019'd;
%DO ADD=&start. %TO &end.;
CREATE TABLE compare_&ADD. as
SELECT T2.DATE, T2.ACCOUNT, T2.VAL_HASH FROM TABLE1 T1, TABLE2 T2
WHERE T1.ACCOUNT = T2.ACCOUNT
AND T1.DATE = "&DATE_FROM"d AND T2.DATE = "&DATE_PLUS1"d
AND T1.VAL_HASH <> T2.VAL_HASH;
%let DATE_FROM=intnx('DAY',&DATE_FROM,1);
%let DATE_PLUS1=intnx('DAY',&DATE_PLUS1,1);
%END
QUIT;
%mend;
%sqlloop(start=1, end=5)

Re: Knowing if a data was update in a range of dates

blueskyxyz — Tue, 18 May 2021 15:00:27 GMT

if you want check the result is not same for two datasets, recommand to use proc compare