BookmarkSubscribeRSS Feed
Andalusia
Obsidian | Level 7

I have two datasets `HAVE` and `HAVE2`. I'm trying to add the column last_color, second_last_color, third_last_color etc. until eight_last_color for every type. last_color is the color which is mentioned in the last quarter (so max quarter). second_last_color is the color which is mentioned in the second_last_quarter (so one before the max) etc. Now in my current code I was only able to calculate the last and second_last_color, I dont know how to calculate until the eight_last_color. But I think I'm very close...

 

These are my 2 datasets:

data have;
infile datalines truncover;
input type $ quarter phase colors $ creation_date :yymmdd6.;
format creation_date yymmdd6.;
datalines;
K-11 201904 1 blue 20200101
K-11 202001 1 blue 20200101
K-11 202002 2 blue 20200101
K-11 202003 3 blue 20200101
K-11 202004 3 black 20200101
K-11 202101 1 blue 20200101
K-11 202102 2 blue 20200101
K-11 202103 3 blue 20200101
K-11 202104 3 black 20200101
K-12 201904 3 blue 20200101
K-12 202001 3 blue 20200101
K-12 202002 1 white 20200101
K-12 202003 1 white 20200101
K-12 202004 1 yellow 20200101
K-12 202101 3 blue 20200101
K-12 202102 1 white 20200101
K-12 202103 1 white 20200101
K-12 202104 1 yellow 20200101
K-13 201904 2 green 20200101
K-13 202001 3 green 20200101
K-13 202002 2 red 20200101
K-13 202003 2 blue 20200101
K-13 202004 2 green 20200101
K-13 202101 3 green 20200101
K-13 202102 2 red 20200101
K-13 202103 2 blue 20200101
K-13 202104 2 blue 20200101
;
data have2;
  infile datalines truncover;
  input type $ quarter;
  datalines;
K-11 -1
K-12 2
K-13 3
;

This is my code:

data long;
set
  work.HAVE
;
run;
proc transpose
  data=long
  out=wide (
    drop=_name_
    where=(type ne "")
  )
  prefix=q_
;
by type;
var phase;
id quarter;
run;
data calc_yearquarter;
merge
  wide
  work.have2
;
by type;
run;
data temp;
merge work.HAVE work.HAVE(firstobs=3 keep=type rename=(type=_type));
if type ne _type;
drop _type;
run;
data temp;
 set temp;
 by type;
 if first.type then n=0;
 n+1;
 if n=1 then id='second_last_advice';
  else id='last_advice';
run;
proc transpose data=temp out=calc_advice(drop=_name_);
by type ;
id id;
var colors;
run;
data SAS11
;
merge
  calc_yearquarter
  calc_advice
;
by type;
run;

So to conclude, right now I have calculated 2 quarters back. I want to calculate 8 quarters back...

4 REPLIES 4
yabwon
Onyx | Level 15

something like this?

data have;
infile datalines truncover;
input type $ quarter phase colors $ creation_date :yymmdd6.;
format creation_date yymmdd6.;
datalines;
K-11 201904 1 blue 20200101
K-11 202001 1 blue 20200101
K-11 202002 2 blue 20200101
K-11 202003 3 blue 20200101
K-11 202004 3 black 20200101
K-11 202101 1 blue 20200101
K-11 202102 2 blue 20200101
K-11 202103 3 blue 20200101
K-11 202104 3 black 20200101
K-15 202101 3 green 20200101
K-15 202102 2 red 20200101
K-15 202103 2 blue 20200101
K-15 202104 2 blue 20200101
K-12 201904 3 blue 20200101
K-12 202001 3 blue 20200101
K-12 202002 1 white 20200101
K-12 202003 1 white 20200101
K-12 202004 1 yellow 20200101
K-12 202101 3 blue 20200101
K-12 202102 1 white 20200101
K-12 202103 1 white 20200101
K-12 202104 1 yellow 20200101
K-13 201904 2 green 20200101
K-13 202001 3 green 20200101
K-13 202002 2 red 20200101
K-13 202003 2 blue 20200101
K-13 202004 2 green 20200101
K-13 202101 3 green 20200101
K-13 202102 2 red 20200101
K-13 202103 2 blue 20200101
K-13 202104 2 blue 20200101
K-14 202101 3 green 20200101
K-14 202102 2 red 20200101
K-14 202103 2 blue 20200101
K-14 202104 2 blue 20200101
;
run;

%let lookback = 8;

data want (rename=(lag_type=type));

  do point = nobs to 1 by -1, nobs;
    set have point = point nobs=nobs;
    lag_type = lag(type);

    array color_last_[&lookback.] $ 12;

    if lag_type ne type then 
      do;  
        if lag_type ne " " then output;
        _N_ = 1;
        call missing(of color_last_{*});
      end;

    if _N_ <= &lookback. then color_last_[_N_] = colors;
    _N_ + 1;
  end;

stop;
keep lag_type color_last_:;
run;

B.

_______________
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

But how to do it in combination with my existing code?

yabwon
Onyx | Level 15

what is the purpose of have2 data set?

what final result are you expecting?

 

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



Pepijn
Fluorite | Level 6

I suck at code but if I interpret your question correctly you can sort your data and create a new column using a 'first' function and count the rows within a specific group...

sort your data descending (last color on top)

count per group with a rank number +1 for every new instance until you encounter the next group (The K-11,K-12 in your data set I think?)

And then only filter the rows labelled 1 till 8 and reverse them again if needed.

data "have";

     SET "output tabel";

by "K-11 colunm name";

RANK +1;

     if first."K-11 colum name" then RANK = 1;

RUN;

first sort it the right way, and maybe add other column to your BY group when needed.

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!

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