I have the following dataset: 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 ; I want the column `colors` to be converted to a numeric value so I can use it as a measurement inside a crosstab in SAS Visual Analytics. How can this be done easily? ... View more

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... ... View more

This is my current dataset (an example so you can reproduce my case, the real one is much bigger): data have; infile datalines truncover; input type $ model $; datalines; XX-1 Light XX-2 Heavy XX-3 Heavy XX-4 Heavy XX-5 Slim XX-6 Light XX-7 Heavy ; I want to add two lines of data to it: XX-8 Light XX-9 Slim   ... View more