BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cello23
Quartz | Level 8

Hello everybody ,

i have this dataset:

prima.bmp

I would like to have this result (if IDCAB  is blank must take the value of the previous cell):

dopo.bmp

I use this query:
data TABLE_1;

set  TABLE_1;

n=_n_;

if missing(IDCAB) then do;

do until (not missing(IDCAB));

n=n-1;

set TABLE_1(keep=IDCAB) point=n;

end;

end;

run;

it works, but with "big dataset" (such as 600,000 records) it crashes.

Please, Can you suggest an alternative?

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
ieva
Pyrite | Level 9

Try:

data table_2 (drop=idcab rename=idcab_mod=idcab);

set table_1;

retain idcab_mod ;

if idcab ne '' then idcab_mod =idcab;

run;

Smiley Happy

View solution in original post

10 REPLIES 10
ieva
Pyrite | Level 9

Try:

data table_2 (drop=idcab rename=idcab_mod=idcab);

set table_1;

retain idcab_mod ;

if idcab ne '' then idcab_mod =idcab;

run;

Smiley Happy

Cello23
Quartz | Level 8

YES!!!!!

" i love you IEVA" !!!  :smileylaugh:

thank you!!!   

Cello23
Quartz | Level 8

Sorry...!!!

I have another similar question...

i have this dataset:

last.jpg

I would like to have this result (if IDCAB  is blank and AID is equal to the previous cell,

IDCAB is  must take the value of the previous cell, If this is not true the cell should be blank):

first.jpg

This dont' work...

data TABLE_1(drop=IDCAB AID  rename=IDCAB_MOD=IDCAB rename=AID_MOD=AID);

set TABLE_2;

retain IDCAB_MOD AID_MOD ;

if (IDCAB ne '' AND AID_MOD=AID) then IDCAB_MOD =IDCAB;

run;

Please help me.

Thank you.

art297
Opal | Level 21

For the cells that you want missing, the aid numbers are the same in your sample data, but different in your desired output.  Which is correct?

ieva
Pyrite | Level 9

This could be the solution you are looking for:

proc sort data=table_1;

by aid descending idcab;

run;

data table_2 (drop=idcab rename=idcab_mod=idcab);

set table_1;

by aid descending idcab;

retain  idcab_mod ;

if first.aid  then idcab_mod =idcab;

run;

Linlin
Lapis Lazuli | Level 10

data have;

  infile cards missover;

  input aid $ idcab $;

cards;

1 ad

1 bb

1

2 fv

2

2

2 cv

;

run;

data want (drop=idcab rename=idcab_mod=idcab);

set have;

by aid;

retain idcab_mod ;

if (first.aid or idcab ne '' )then idcab_mod =idcab;

run;

art297
Opal | Level 21

Presuming that your output file contained the correct values, and that your file is already in the order shown in your example, then the following would do (I think) what you want:

data table_2 (drop=hold:);

  set table_1;

  by aid notsorted;

  retain hold_idcab;

  if first.aid then hold_idcab=idcab;

  else idcab=hold_idcab;

run;

Cello23
Quartz | Level 8

sorry for my mistake, output file container the correct values.

your support is really precious !!!!

Thanks to all!!!

Linlin
Lapis Lazuli | Level 10

Dear  Art,

I ran your code. It did not have the correct outcome for the last observation.

data table_1;

  infile cards missover;

  input aid $ idcab $;

cards;

1 ad

1 bb

1

2 fv

2

2

2 cv

;

run;

data table_2 (drop=hold:);

  set table_1;

  by aid notsorted;

  retain hold_idcab;

  if first.aid then hold_idcab=idcab;

  else idcab=hold_idcab;

run;

proc print;

run;

                                      Obs    aid    idcab

                                        1      1      ad

                                        2      1      ad

                                        3      1      ad

                                        4      2      fv

                                        5      2      fv

                                        6      2      fv

                                        7      2      fv

art297
Opal | Level 21

The OP didn't have such a condition in the example data, thus I decided that it would be presumptive to include one.  Obviously, if the OP wanted non-missing values were to be left alone, the decision would also have to be made regarding whether such new values should be assigned to later repititions as well.

Soapbox: Unfortunately, programmers often write code to cover conditions that are outside of the defined requirements, rather than simply mentioning the possible discrepancy(ies) and asking how it/they should be handled.

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
  • 10 replies
  • 1461 views
  • 3 likes
  • 4 in conversation