Hello All,
I am trying to create a macro for the following problem: For each ID, I want to just retrieve those which changed from no decimal to decimal and then back to no decimal points.
Kindly find attached the excel attached. Could anyone please suggest me a array which would retrieve the records (highlighted grey in excel).
Kind regards
You likely are looking for a solution involving LAG not array as array works only on variables in one record at a time.
Unless you want to transpose the data and then transpose back.
Thank you. This is helpful. I tried transpose:
data test;
infile datalines;
input ID TIME monyy5. Point;
datalines;
1 Jan10 1
1 Feb10 2
1 Mar10 2.1
1 Apr10 2
1 May10 2
1 Jun10 2.1
1 Jul10 2
1 Aug10 2.1
2 Jan10 1
2 Feb10 1
2 Mar10 2
2 Apr10 2.1
2 May10 2.1
3 Jan10 1
3 Feb10 2
3 Mar10 2.1
3 Apr10 3
;
proc print;
run;
data test1;
set test;
format TIME monyy5.;
proc print;
run;
Proc transpose data=test1
out=tests(drop=_name_) prefix=pt;
By ID;
Var POINT;
Run;
I have a big dataset so per ID I may have say 1000 observations. Would it be possible to have a macro which creates a dataset in which LOOPS for each ID on pt1-ptn and keep only cases which have changed from from no decimal to decimal. For e.g. in this example, it would be pt3 (2.1),pt4(2) & pt6 (2.1),pt7(2) for ID = 1; pt3(2.1),pt4(3) for ID = 2.
Regards
I also tried to achieve the results, but didnt work-
%macro A;
data testss;
set tests;
by ID;
array pt (20) _numeric_ pt1 - pt20;
array t (20) _numeric_ t1-t20;
%do i = 1 %to 20;
if pt(i) in (1.1,2.1,2.2) and pt(i + 1) in (1,2) then do;
t(i) = 1;
end;
%end;
run;
%mend A;
%A;
Any help will be much appreciated.
Hi,
As your data is currently normalized, why not use SQL to get the information:
data test;
infile datalines;
input ID TIME monyy5. Point;
datalines;
1 Jan10 1
1 Feb10 2
1 Mar10 2.1
1 Apr10 2
1 May10 2
1 Jun10 2.1
1 Jul10 2
1 Aug10 2.1
2 Jan10 1
2 Feb10 1
2 Mar10 2
2 Apr10 2.1
2 May10 2.1
3 Jan10 1
3 Feb10 2
3 Mar10 2.1
3 Apr10 3
;
run;
proc sql;
create table WANT as
select BASE.ID,
BASE.TIME,
BASE.POINT
from WORK.TEST BASE
where BASE.POINT - int(BASE.POINT) > 0
and EXISTS(select distinct
THIS.ID
from WORK.TEST THIS
where THIS.ID=BASE.ID
and THIS.TIME > BASE.TIME);
quit;
The above only gives you the actual y.x result, but its straight forward to find the next or previous row = min(time) > base.time.
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 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.