topic Re: SAS matching two columns together in New SAS User

SAS matching two columns together

UMR13 — Fri, 26 Jul 2019 19:57:22 GMT

Hello,

I was wondering if SAS had the capability of matching two columns together for me. I have a School Name column and a Academic Year column. Each school has multiple years associated with it but they are all separate:

Example: School 1/ 2018-2019

School 1/ 2013-2014

I would like for SAS to give me a combined list in the form this format: School 1/ 2013-2014,2018-2019 and so on. Is this possible?

Thank you.

Re: SAS matching two columns together

Reeza — Fri, 26 Jul 2019 20:00:53 GMT

Yes it's possible. If you need help on how to do so please provide some more details on what your input data looks like and what you expect as output. Sample data helps a lot.

Re: SAS matching two columns together

UMR13 — Fri, 26 Jul 2019 20:12:39 GMT

I have added a SAS Capture of what it looks like at this moment. I would like for example all the years to be in one cell together with one school.

Example: ADA Merritt K-8 Center in one cell with no duplicates and the years all in one cell as 2010-2011,2011-2012,2012-2013, etc... all in one cell.

Re: SAS matching two columns together

Reeza — Fri, 26 Jul 2019 20:20:40 GMT

Sorry, can't open attachments, someone else will have to help 😞

Re: SAS matching two columns together

ballardw — Fri, 26 Jul 2019 23:16:06 GMT

proc sql;
   create table temp as
   select distinct School_name, Academic_year
   from have
   order by School_name, Academic_year
   ;
quit;

Proc transpose data=temp out=trans;
   by school_name;
   var Academic_year;
run;

data want; 
   set trans;
   length Yearlist $ 200; /* this needs to be set long enough to hold longest expected list which would be roughly 10 times the numer of academic years 
that might be involved)
   Yearlist = catx(',', of col: );
   drop col: ;
run;

This code is using a variable list Col: which means all of the variables whose names start with Col . The colon must be immediately after the last letter of the name stem.

The length of the yearlist is # of academic years* (9 +1) . The 9 is how many characters to hold something like 2016-2017, plus 1 for the comma =>10* the number of academic years that might be involved. Look at the TRANS set to see how many years. There will be variables named COL1 -COLn, one for each year. The school with the most years will set the maximum number of years.

Untested because no actual data set provided.