BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Andalusia
Obsidian | Level 7
data have;
infile datalines truncover;
input type $ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1  
XX-3 1 3 
XX-4 3 4 
XX-5 4 2 3
XX-6 4 
XX-7 2 
XX-8 2 1 4

I want to add a new column named `last_value`. This column should contain the last value for every row based on the last quarter. So for example XX-1 its `last_value` is `3`.  The last value of XX-6 is `4`. This dataset is continue growing. So there is a possibilty that in quarter 4 the last_value of XX-1 changes...

1 ACCEPTED SOLUTION

Accepted Solutions
japelin
Rhodochrosite | Level 12
data want;
  set have;
  array q{*} q_:;
  do i= dim(q) to 1 by -1;
    if q{i} ne . then last_value=q{i};
    if q{i} ne . then leave;
  end;
  drop i;
run;

If possible, I would like a few obs of large datasets to check my code works or not.

View solution in original post

11 REPLIES 11
japelin
Rhodochrosite | Level 12

how about this code?

data want;
  set have;
  array q{*} q_:;
  do i=1 to dim(q);
    if q{i}=. then leave;
    last_value=q{i};
  end;
  drop i;
run;
Andalusia
Obsidian | Level 7

I tried your code on a larger dataset and it doesnt seem to always work.... take a look for example to row 4 and 5 in the red box it says 2. For row 4 the last measured quarter is q_202102 and that has value 1, for row 5 the last measured quarter is q_202103 and that one also has value 1. And what about the empty ones, those should be filled to because the data is available.

Andalusia_0-1630487457269.png

 

japelin
Rhodochrosite | Level 12
data want;
  set have;
  array q{*} q_:;
  do i= dim(q) to 1 by -1;
    if q{i} ne . then last_value=q{i};
    if q{i} ne . then leave;
  end;
  drop i;
run;

If possible, I would like a few obs of large datasets to check my code works or not.

Kurt_Bremser
Super User

Do not use wide datasets, and store dates and date-related values as SAS dates, so you can use them in calculations.

So, first convert your dataset to something that is much more useful:

data have;
infile datalines truncover;
input type $ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1  
XX-3 1 3 
XX-4 3 4 
XX-5 4 2 3
XX-6 4 
XX-7 2 
XX-8 2 1 4
;

proc transpose data=have out=long1 (where=(col1 ne .));
by type;
var q:;
run;

data have_long;
set long1;
rename col1=value;
quarter = input(catx("Q",substr(_name_,3,4),substr(_name_,7)),yyq6.);
format quarter yyq6.;
drop _name_;
run;

From that, finding the last is dead simple:

data want;
set have_long;
by type quarter;
if last.type;
run;
Andalusia
Obsidian | Level 7
I see your solution, its an option but I really need the quarters to stay as they are. I would like it as the answer of @japelin but then working obviously
Kurt_Bremser
Super User

For human consumption, you can always use PROC REPORT to display the data in a wide layout (or export it to Excel for the PHB with ODS), but for most of your work the long dataset layout is the way to go, unless you have a strong masochistic urge in you 😉

yabwon
Onyx | Level 15

How about that:

data have;
infile datalines truncover;
input type $ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1  
XX-3 1 3 
XX-4 3 4 
XX-5 4 2 3
XX-6 4 
XX-7 2 
XX-8 2 1 4
;
run;
proc print;
run;

data want;
  set have;
  last = coalesce(q_20213,q_20212,q_20211);
run;
proc print;
run;

 

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Andalusia
Obsidian | Level 7
@yabwon and what if in a few months time I get the data of q_20214? I cant be changing the code every time a new quarter comes in....
yabwon
Onyx | Level 15

You can always make it "macro-dynamic":

data have;
infile datalines truncover;
input type $ q_20211 q_20212 q_20213;
datalines;
XX-1 1 2 3
XX-2 1  
XX-3 1 3 
XX-4 3 4 
XX-5 4 2 3
XX-6 4 
XX-7 2 
XX-8 2 1 4
;
run;
proc print;
run;

proc transpose data = have(obs=0 keep=q_:) out = list(keep=_name_);
  var _all_;
run;

data _null_;
  length list $ 500;
  retain list;
  set list end = end;
  list = catx(",",_NAME_,list);
  if end then call symputx("list", list, "G");
run;

options symbolgen;
data want;
  set have;
  last = coalesce(&list.);
run;
proc print;
run;

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Andalusia
Obsidian | Level 7
Almost.... I need a dataset in return not a SAS report...
Kurt_Bremser
Super User

@Andalusia wrote:
@yabwon and what if in a few months time I get the data of q_20214? I cant be changing the code every time a new quarter comes in....

That is exactly why you use a long dataset layout; new data is then data and not structure.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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