BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASdevAnneMarie
Barite | Level 11

Hello Experts,

 

I would like to replace the empty rows with the last known data and create the column MT_CORR, but my code doesn't work.

 

 

My code is :

Data matable3;
	set matable2;
	by Numero Code2;
	if first.Numero and first.Code then 
	MT_CORR=MT_2;
    retain MT_CORR;
	;
run;

 I have joined the file.

 

 

SASdevAnneMarie_0-1710088496194.png

Thank you for you help. 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

Do selective updating of mt_corr, then selective updating of mt_2.  This program is untested, but it should fill in the yellow cells in your data:

 


data matable3;
  set matable2;
  by numero code2;
  retain mt_corr;
  if first.code2 then mt_corr=.;
  if mt_2^=. then mt_corr=mt_2;
  else mt_2=mt_corr;
run;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

2 REPLIES 2
mkeintz
PROC Star

Do selective updating of mt_corr, then selective updating of mt_2.  This program is untested, but it should fill in the yellow cells in your data:

 


data matable3;
  set matable2;
  by numero code2;
  retain mt_corr;
  if first.code2 then mt_corr=.;
  if mt_2^=. then mt_corr=mt_2;
  else mt_2=mt_corr;
run;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

Just use the UPDATE statement.

 

If you want to carry forward all of the variables then use:

data matable3;
  update matable2(obs=2) matable2;
  by numero code2;
  output;
run;

If there are some that you don't want the missing values replaced with the previous value then re-read those variables.

data matable3;
  update matable2(obs=2) matable2;
  by numero code2;
  set matable2(keep=other);
  output;
run;

PS No need (or desire) to post on XLSX file to share example data.  A simple data step is easier to create and very much easier for others to use to create your example data.

data have;
  input numero $ code2 $ date :yymmdd. mt_2 expect;
  format date yymmdd10.;
cards;
CC1 X 2019-07-18 204167.73653 204167.73653
CC1 X 2019-08-06 . 204167.73653
CC1 X 2019-08-06 . 204167.73653
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-09-30 313986.57001 313986.57001
CC1 X 2019-12-10 313986.57001 313986.57001
CC1 X 2019-12-10 313986.57001 313986.57001
CC1 X 2019-12-31 339560.10139 339560.10139
CC1 X 2019-12-31 . 339560.10139
CC1 X 2019-12-31 . 339560.10139
PPK LT 2019-07-18 5000.7365312 5000.7365312
PPK LT 2019-08-06 . 5000.7365312
PPK LT 2019-08-06 . 5000.7365312
PPK LT 2019-09-30 . 5000.7365312
PPK LT 2019-12-31 . 5000.7365312
PPK LT 2019-12-31 7000.1013906 7000.1013906
PPK LT 2019-12-31 7050.1013906 7050.1013906
;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 314 views
  • 2 likes
  • 3 in conversation