Is there a reason you're using `options validvarname = any`? If you don't use that, SAS will prepend it with an underscore. You can then use that pattern to do whatever you need to do in your DATA step.   options validvarname=v7; data work.DATA; input PRODUCT MONTH $ AMT; datalines; 1 202201 100 1 202202 175 2 202202 200 ; run; proc transpose data= work.DATA out= work.HAVE (drop= _NAME_); id MONTH; by PRODUCT; var AMT; run; data work.WANT; set work.HAVE; array MONTHS [*] _:; do i = 1 to dim(MONTHS); x = MONTHS[i] + 1; end; run; If you need to impose the date values as columns, maybe do it after your DATA step? Then write it out? I know certain people prefer to have reports made a certain way, so that may be the case.   options validvarname = any; data want2; set want; run; ... View more

What determines `pos`? Please provide some example data. ... View more

Okay, I see that you mentioned months in here, and that would change things drastically. Accordingly, this solution may not work if you need this sorted by month. This seems to fit your parameters, but I see it as a bit clunky. I don't really like mixing DATA steps and PROC SQL, but sometimes they work well together.   data have; infile datalines delimiter = ' '; input id date :date9. code :$2. flag cnt; format date date9.; datalines; 1 30NOV2021 B1 1 1 1 31OCT2021 X1 0 0 1 30SEP2021 Z1 0 0 1 31AUG2021 A1 1 1 1 31JUL2021 A1 1 2 1 30JUN2021 A1 1 3 2 30NOV2021 B1 1 1 2 31OCT2021 B1 1 2 2 30SEP2021 B1 1 3 2 31AUG2021 Z1 0 0 2 31JUL2021 A1 1 1 2 30JUN2021 A1 1 2 3 30NOV2021 B1 1 1 3 31OCT2021 B1 1 2 3 30SEP2021 Z1 0 0 3 31AUG2021 Z1 0 0 3 31JUL2021 A1 1 1 3 30JUN2021 A1 1 2 4 30NOV2021 B1 1 1 4 31OCT2021 B1 1 2 4 30SEP2021 B1 1 3 4 31AUG2021 B1 1 4 4 31JUL2021 B1 1 5 4 30JUN2021 B1 1 6 5 30NOV2021 A3 1 1 5 31OCT2021 A2 1 2 5 30SEP2021 A1 1 3 5 31AUG2021 A1 1 4 5 31JUL2021 A1 1 5 5 30JUN2021 A1 1 6 ; run; data have_subset; set have (keep = id date code); by id; if first.id then seq = 1; if id = lag(id) and code ~= lag(code) then seq + 1; if code in ('A1', 'B1') then flag = 1; else flag = 0; run; proc sql; create table want as select id, date, code from have_subset where flag = 1 group by id having seq = min(seq); quit;   ... View more

I'm not quite understanding your question. When I look at this, you could just filter where code = `B1` to get what you need. But I'm thinking that there may be additional patterns that are not shown in your example data.   Additionally, it helps us to have a reproducible example in the following format. A screenshot isn't helpful for us to resolve your issue, and we can often make mistakes trying to replicate what you have posted in a screenshot.   data have; infile datalines delimiter = ' '; input id date :date9. code :$2. flag cnt; format date date9.; datalines; 1 30NOV2021 B1 1 1 1 31OCT2021 X1 0 0 1 30SEP2021 Z1 0 0 1 31AUG2021 A1 1 1 1 31JUL2021 A1 1 2 1 30JUN2021 A1 1 3 2 30NOV2021 B1 1 1 2 31OCT2021 B1 1 2 2 30SEP2021 B1 1 3 2 31AUG2021 Z1 0 0 2 31JUL2021 A1 1 1 2 30JUN2021 A1 1 2 3 30NOV2021 B1 1 1 3 31OCT2021 B1 1 2 3 30SEP2021 Z1 0 0 3 31AUG2021 Z1 0 0 3 31JUL2021 A1 1 1 3 30JUN2021 A1 1 2 ; run; You can also use this to make a DATALINES statement from your actual data. ... View more

What is 'far'? Is it two years ahead or two years behind? Here's an example you can work with and tell us what you want to do.   data have; input id date :mmddyy10.; format date mmddyy10.; datalines; 1 11/12/2019 2 05/05/2020 3 12/31/2019 4 12/31/2018 5 12/31/2020 6 03/04/2022 7 04/05/1980 8 12/31/2022 ; run; I ended up using the YRDIF function to compute the actual days between the two. data want; set have; years_from = yrdif("31DEC2021"d, date, 'actual'); run; You can then use FLOOR or CEIL functions to get what you want since we don't know. data want; set have; years_from = yrdif("31DEC2021"d, date, 'actual'); years_ceil = ceil(years_from); years_floor = floor(years_from); run;   It's really important that we know what you want. Please always provide example data like I have above. Also provide a data set showing exactly what you want. It allows people to answer the questions and increases the likelihood of people answering your questions.   ... View more

Alright, I misunderstood your question. Do you mind replacing the `.` in the id field in this DATALINES statement with exactly what you want? data want; input subject :$20. id; datalines; S1-1-METAPH-002 . S1-1-METAPH-019 . S1-1-METAPH-089 . S1-1-METAPH-027 . S1-1-METAPH-006 . S1-1-METAPH-007 . S1-1-METAPH-008 . S1-1-METAPH-003 . S3-1-METAPH-027 . S3-1-METAPH-008 . S3-1-METAPH-089 . S3-1-METAPH-003 . S3-1-METAPH-002 . S3-1-METAPH-006 . S3-1-METAPH-007 . S3-1-METAPH-019 . S5-1-METAPH-002 . S5-1-METAPH-003 . S5-1-METAPH-027 . S5-1-METAPH-089 . S5-1-METAPH-006 . S5-1-METAPH-019 . S5-1-METAPH-008 . S5-1-METAPH-007 . ; run; You can copy this code, and in your reply use the `insert SAS code` button and replace the `.` with your desired values.   This will help us figure out the solution. ... View more

Is that the only pattern that we need to be aware of? In other words, is it S[digit]-[number]? If so, this is a bit clunky but works for this instance.  data have; input subject $20.; datalines; S1-1-METAPH-002 S1-1-METAPH-019 S1-1-METAPH-089 S1-1-METAPH-027 S1-1-METAPH-006 S1-1-METAPH-007 S1-1-METAPH-008 S1-1-METAPH-003 S3-1-METAPH-027 S3-1-METAPH-008 S3-1-METAPH-089 S3-1-METAPH-003 S3-1-METAPH-002 S3-1-METAPH-006 S3-1-METAPH-007 S3-1-METAPH-019 S5-1-METAPH-002 S5-1-METAPH-003 S5-1-METAPH-027 S5-1-METAPH-089 S5-1-METAPH-006 S5-1-METAPH-019 S5-1-METAPH-008 S5-1-METAPH-007 ; run; proc sort data = have; by subject; run; data have_2; set have; id = catx("-", scan(subject, 1, '-'), scan(subject, 2, '-')); run; proc sort data = have_2; by id; run; data want; set have_2; by id; retain new_id; if first.id then new_id + 1; run; Obs subject id new_id 1 S1-1-METAPH-002 S1-1 1 2 S1-1-METAPH-003 S1-1 1 3 S1-1-METAPH-006 S1-1 1 4 S1-1-METAPH-007 S1-1 1 5 S1-1-METAPH-008 S1-1 1 6 S1-1-METAPH-019 S1-1 1 7 S1-1-METAPH-027 S1-1 1 8 S1-1-METAPH-089 S1-1 1 9 S3-1-METAPH-002 S3-1 2 10 S3-1-METAPH-003 S3-1 2 11 S3-1-METAPH-006 S3-1 2 12 S3-1-METAPH-007 S3-1 2 13 S3-1-METAPH-008 S3-1 2 14 S3-1-METAPH-019 S3-1 2 15 S3-1-METAPH-027 S3-1 2 16 S3-1-METAPH-089 S3-1 2 17 S5-1-METAPH-002 S5-1 3 18 S5-1-METAPH-003 S5-1 3 19 S5-1-METAPH-006 S5-1 3 20 S5-1-METAPH-007 S5-1 3 21 S5-1-METAPH-008 S5-1 3 22 S5-1-METAPH-019 S5-1 3 23 S5-1-METAPH-027 S5-1 3 24 S5-1-METAPH-089 S5-1 3 ... View more

You should use the `length = ` option in a PROC SQL query. Here's an example -- your code is pretty long so I just made a fake example: proc sql; create table want as select *, case when make = "Toyota" then "It's a Toyota!" when make = "Ford" then "Ford!" else "What kind of car is this?" end as test_var length = 25 from sashelp.cars; quit; Unless you have a format that's already specified in a PROC FORMAT step. ... View more

That's a name literal, and it isn't a valid name. I'm assuming you have  options validvarname = any; specified somewhere.   You have to refer to it like this: "1"n options validvarname = any; data want; input '1'n; datalines; 11 11 . 11 11 ; run; proc means data = want; var '1'n; run; Probably a good idea to rename those to avoid the hassle of name literals. ... View more

Always there with the better insight! Thanks, Tom! ... View more