Do Loop

[Eco]

Hi everyone, I have a question about loop. Here I have a sample data set:

data sample;
input stkcd $ ViolationYear $30.;
datalines;
000003 2001
000004 2008,2009,2010
000007 2011,2012,2013,2014
;
run;

I want to split the year in ViolationYear and rearrange to be like one stkcd match one ViolationYear liike:

000003 2001

000004 2008

000004 2009

000004 2010

000007 2011

000007 2012

and so on.

I am wondering if I can use a loop to achieve this or is there any other better choice? Looking forward to your suggestions.

Best

[Tom · Accepted solution]

There is no need for MACRO code for this problem.  You don't need to generate code statements.  Just write normal SAS code to generate the observations you want.

data sample;
  input stkcd :$10. ViolationYear :$30.;
datalines;
000003 2001
000004 2008,2009,2010
000007 2011,2012,2013,2014
;

data want ;
  set sample;
  do sample_no=1 by 1 until (sample_no=countw(ViolationYear,','));
     Year = input(scan(ViolationYear,sample_no,','),32.);
     output;
  end;
  drop ViolationYear;
run;

proc print;
run;

Result:

                 sample_
Obs    stkcd        no      Year

 1     000003       1       2001
 2     000004       1       2008
 3     000004       2       2009
 4     000004       3       2010
 5     000007       1       2011
 6     000007       2       2012
 7     000007       3       2013
 8     000007       4       2014

[Eco]

Thank you very much Tom, it seems I have made the queation complicate. 

[Eco]

By the way, is there any way that I can find such kind of functions or to learn them systematiclly? I think these functions are so tiny that I cannot find them easily... 

[PaigeMiller]

---

@Eco wrote:

By the way, is there any way that I can find such kind of functions or to learn them systematiclly? I think these functions are so tiny that I cannot find them easily... 

---

This is a very vague question, and yes, there are a gajillion books and tutorials available on SAS that will help you learn, plus comprehensive online documentation.

One helpful book is called "The Little SAS Book". Some others are listed here: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=lefunctionsref&docsetTarg...

[Eco]

Oh yes, The Little SAS Book. I heard about it before but I haven't tried it. The reference books that I used from now are my university's teaching materials which are not enough for now. It seems to be a long journey for me to learn SAS. Thank you for your suggestion~

[Kurt_Bremser]

Maxim 1: Read the Documentation. For functions, go here: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarg...

[Kurt_Bremser]

Nothing to do with macro (that's why I changed the subject line).

But it's done with a data step do loop and some string functions:

data want;
set sample;
do i = 1 to countw(violationyear,',');
  year = input(scan(violationyear,i,',');
  output;
end;
drop i violationyear;
run;

---

@Eco wrote:

Hi everyone, I have a question about loop. Here I have a sample data set:

data sample;
input stkcd $ ViolationYear $30.;
datalines;
000003 2001
000004 2008,2009,2010
000007 2011,2012,2013,2014
;
run;

I want to split the year in ViolationYear and rearrange to be like one stkcd match one ViolationYear liike:

000003 2001

000004 2008

000004 2009

000004 2010

000007 2011

000007 2012

and so on.

I am wondering if I can use a loop to achieve this or is there any other better choice? Looking forward to your suggestions.

Best

---

[Eco]

Thank you Kurt, it works~ It seems I have made the question complicate.