BookmarkSubscribeRSS Feed
Siddharth123
Obsidian | Level 7

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

4 REPLIES 4
ballardw
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1194 views
  • 0 likes
  • 3 in conversation