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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.