SAS Array

[Siddharth123]

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

[ballardw]

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.

[Siddharth123]

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

[Siddharth123]

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.

[RW9]

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.