BookmarkSubscribeRSS Feed
Calcite | Level 5



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. 

Super User
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.
Calcite | Level 5

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. 

Super User
Sorry, can't open attachments, someone else will have to help 😞
Super User
proc sql;
   create table temp as
   select distinct School_name, Academic_year
   from have
   order by School_name, Academic_year

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

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: ;

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.


Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 3 in conversation