topic Re: Best way to get to common dates in SAS Enterprise Guide

Best way to get to common dates

crawfe — Sat, 25 May 2019 14:19:49 GMT

Hi. I use SAS EG. I have two small tables. Table A: DateA,Variable A and Table B: DateB, Variable B.

I would like to end up with a Joined Table: Date_MMYYYY Var. A Var. B.

To do this I need to (I think) convert both DateA, DateB to a real DateMMYYYY to have a common Join point.

The format change is not good enough (behind the scenes, the dates are still DDMMYYY).

Can someone help? Thanks! ( I can only think of clumsy, long ways around).

Re: Best way to get to common dates

VDD — Sat, 25 May 2019 14:37:37 GMT

data Data1;
input ID N value1;
cards;
12 1 56
12 2 45
12 3 65
25 1 87
25 2 98
25 3 23
;

data _null_;
	date1=input(put(today(),yymmdd8.),yymmdd8.);
 	my_date = intnx('month',date1,0,'s');
	call symput('my_month',put(my_date,mmyyn6.));
put date1;
put "MY date :" my_date;
	format my_date my_month mmyyn6.;
run;
data data1&my_month.;
	set Data1;
run;

Re: Best way to get to common dates

PGStats — Sat, 25 May 2019 22:15:25 GMT

To clarify what you are starting with, please run the following statements:

proc print data=A(obs=10) noobs; format _all_; run;
proc print data=B(obs=10) noobs; format _all_; run;

and post the results with the {i} icon.

Re: Best way to get to common dates

crawfe — Sun, 26 May 2019 12:55:25 GMT

VDD, thanks. It would also be helpful to me and others if you could explain your logic?

PG Stats: They are just regular dates. DateA = 01JAN2019, DateB = 27JAN2019.

I was hoping to do a JOIN at the format level. (i.e.) 01/2019. Didn't work. Got separate lines.

01/2019 --- Var A

---- 01/2019 Var B

I am thinking I have to turn them to a Character first ('01/2019') then the JOIN

would work cleanly then convert the 'Date' back to a real Date (MM/YYYY) to be able to get

them sorted by date.

Re: Best way to get to common dates

VDD — Sun, 26 May 2019 15:11:36 GMT

use the logic and replace today() with your incoming date value from each record to create a new variable that you are requesting.

If you post what you have tried and sample data rather than have us end up in left field we can assist you better.

How to create a data-step version of your data
https://communities.sas.com/t5/help/faqpage/faq-category-id/posting#posting

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

How to Add attachments
https://communities.sas.com/t5/Community-Memo/Adding-attachments-to-your-communities-posts/ba-p/464767

Re: Best way to get to common dates

crawfe — Tue, 28 May 2019 00:41:51 GMT

No, VDD. I meant for you to explain your logic in your code.

Next time....

I simplified the logic a little; it works for the JOIN.

DateChar = PUT(DateA,mmyys7.) /* change to a character mm/yyyy */

BACK_toDate = INPUT(DateChar,ANYDTDTE7.) /* change back to date (num) for sorting*/

Format = mmyys7