BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jose7
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jose7
Obsidian | Level 7
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)

View solution in original post

7 REPLIES 7
blueskyxyz
Lapis Lazuli | Level 10
/*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;
blueskyxyz
Lapis Lazuli | Level 10
plesase upload your sample dataset to test the code easliy
Jose7
Obsidian | Level 7

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

blueskyxyz
Lapis Lazuli | Level 10

捕获.PNG

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

Jose7
Obsidian | Level 7
My filter is "ACCOUNT" and I want to keep the date where the account changes the value of product
Jose7
Obsidian | Level 7
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)
blueskyxyz
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 482 views
  • 0 likes
  • 2 in conversation