Hi all,
I'm currently attempting to merge two databases, one has date level data, the other has month level data and I need the month level data to be repeated for all the days in that month for the first dataset.
for example:
dataset1
ID Date
1 1/1/01
1 1/2/01
1 1/3/01
1 1/4/01
1 1/5/01
1 1/6/01
dataset2
ID Date(month level) Values
1 1/01 5
Final dataset
ID Date Values
1 1/1/01 5
1 1/2/01 5
1 1/3/01 5
1 1/4/01 5
1 1/5/01 5
1 1/6/01 5
This is the code I've tried (ignore the date format as I'm using a diff format in my actual data) but this results in only the first day of each month assuming the value that I want applied to all days of the month.
PROC SQL;
CREATE TABLE Finaldata AS
SELECT A.ID,
A.date FORMAT=YYMMDD8. AS date,
B.Values
FROM dataset1A LEFT JOIN dataset2 B
ON (A.ID= B.ID) AND
(A.date = B.date)
ORDER BY ID, date;
QUIT;
Thanks for your help.
Instead of
AND
(A.date = B.date)
try
where (month(a.date)=month(b.date) and year(a.date)=year(b.date))
Instead of
AND
(A.date = B.date)
try
where (month(a.date)=month(b.date) and year(a.date)=year(b.date))
I would like to us groupformat option.
data want;
merge dataset1 dataset2;
by id date groupformat;
format date monyy5.;
run;
Ksharp
It has been a very long time before I saw 'groupformat' in action! Seems more efficient comparing to sql if running on SAS side.
Haikuo
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.