BookmarkSubscribeRSS Feed
kjohnsonm
Lapis Lazuli | Level 10

Hello all,

I would like to soft code a do loop or any loop for that matter with the 'key' I have built in a small proc sql data set.  I am not sure how or if this is possible help or other ideas please?

proc sql;
create table TERM_TBL_DIST as
select DISTINCT strm||'*'||acad_career as key, STRM, ACAD_CAREER
from &snapshotlib..PS_TERM_TBL
WHERE ACAD_YEAR BETWEEN "&year_four." and "&year.";
quit;
proc print data = TERM_TBL_DIST noobs n;run;

My data set has 103 obs at this time and will change over semesters. Here are samples of my key and two vars that build it.

                      ACAD_
                                   key          STRM    CAREER

                                   2153*UGRD    2153     UGRD
                                   2153*VETM    2153     VETM
                                   2155*MEDI    2155     MEDI
                                   2155*PHAR    2155     PHAR
                                   2155*UGRD    2155     UGRD
                                   2155*VETM    2155     VETM
                                   2157*BUSN    2157     BUSN
                                   2157*GRAD    2157     GRAD
                                   2157*IALC    2157     IALC
                                   2160*UGRD    2160     UGRD
                                   2163*BUSN    2163     BUSN
                                   2163*GRAD    2163     GRAD
                                   2163*IALC    2163     IALC
                                   2163*MEDI    2163     MEDI
                                   2165*BUSN    2165     BUSN
                                   2165*GRAD    2165     GRAD
                                   2165*IALC    2165     IALC


What I am asking is simply how do I "auto" or soft code the v= 1, 2,3,4, … below with my build key:

 

data A;
do v = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 21;
/* my work here */
 output;
end;
run;

 

PS sorry if I have titled this wrong, I don't know what I don't know and all...  LOL

TIA  -KJ

16 REPLIES 16
ballardw
Super User

It is not at all clear what your question may be.

Are you wanting to do something for each value of your key? If so and Key is already in your data then

 

Data A;

   set TERM_TBL_DIST ; /*<= since you created this data set with Key in it*/

   <my work>

run;

will process each record in the TERM_TBL_DIST data set. Data step implies a "loop" through each record in the data set(s) on the SET statement.

 

If you need more help it will help to post a small example of the starting data you have and the desired result along with rules.

 

You might consider using the CATX function instead of the || operators. The || can result in embedded blanks depending on the values of the variables concatenated. Catx ('*',strm, acad_career) as Key

kjohnsonm
Lapis Lazuli | Level 10

Well I am not sure this will help but in my case I have a double nested do loop and the outer loop is hard coded to 1-5 for 5 acad_careers and it really should be soft coded to my key shown before... to build out a calendar of snapshot db dates and cross walk information for the terms/acad_careers for reporting purposes for example -9 weeks before term A. compared to -9 weeks before term B.  bla bla...  I was trying to save the list from a lot of details that are not important to the big picture...

 

 

Anyway here is a bit more verbose code do loop but still very stripped down from my final need.

 

data calendar;

format t_SNAP_DATE date9.;

/* this next line needs to become */

      do j = 1 to 5;

/* efectivly somthing like this  next line at least from a hard coded example I saw on a blog page */

do j = 2137*BUSN, 2137*GRAD, 2137*MEDI, 2137*PHAR, 2137*UGRD, 2137*VETM, 2140*UGRD, 2143*BUSN, 2143*GRAD, 2143*MEDI, 2143*PHAR, 2143*UGRD, 2143*VETM, 2145*BUSN, 2145*GRAD, 2145*MEDI, 2145*PHAR, 2145*UGRD, 2145*VETM, 2147*BUSN;

/* however I do not want it hard coded, like above I want to make it softcoded if posible ?????? 

I want the 'key' to be the control list and to be able to use the two fields &strm. + &acad_career. in the body of my do loop.  And as you can see I do not 'set' a data set in this code I am building it from scrach, my output needs to be keyed on  SNAP_DATE, STRM, and ACAD_CAREER    oops I think my proc sql needs to pass along start date too, however I can do that on my own... that is all I can add at this time,   Does that help???????? */

 

      do i = -386 to &term_day_ct.;

                  STRM=&STRM.;

                                           /*                                 1     2     3     4     5              */

                  ACAD_CAREER=j;   /* will be changed from number to GRAD, MEDI, PHAR, UGRD, VETM, etc later */
                  /* so the above becomes */
                  ACAD_CAREER=&ACAD_CAREER.;

                  /* etc */

                  term_begin_dt=put(Intnx( 'Day' , "&term_begin_d."d, 0 ),date9.);

                  SNAP_DATE=put(Intnx( 'Day' , "&term_begin_d."d, i ),date9.);

                  t_SNAP_DATE=(input(SNAP_DATE,date9.));

                  month=month(t_SNAP_DATE);

     

      output;

      end;

      drop i;

      end;

      drop j;

run ;

ballardw
Super User

Example input data and desired output will clarify a host of issues.

 

Here's a stab at some of what I think you may be attempting.

proc sql noprint;
   select distinct quote(acad_career)  into: al separated by ','
   from &snapshotlib..PS_TERM_TBL;
   select distinct  quote(strm) into: sl separated by ','
   from &snapshotlib..PS_TERM_TBL;
quit;
%put &al &sl;
data junk;
   do s = &sl;
   do a = &al;
   output;
      key = catx('*',s,a);
   end;
   end;
run; 
Astounding
PROC Star

I'm also guessing at your intentions.  Here's one possibility:

 

proc sql noprint;

select "'" || strip(key) || "'" into : key_list separated by ' ' from _ACAD;

quit;

 

That gives you a macro variable holding a list of the keys:

 

'2153*UGRD' '2153*VETM' ....

 

Then you can use the ones you choose by setting up an array:

 

data A;

array keys {&sqlobs} $ 9 _temporary_ (&key_list);
do v = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 21;

    particular_key = keys{v};
/* my work here */
 output;
end;
run;

 

 

kjohnsonm
Lapis Lazuli | Level 10

The "into" key word I have used, but am totally lost with the use of "arrays".   I think of arrays like an old Fortran programmer and don't get the use of them in SAS one bit...

ugggg!

 

I should have thought of the “into” option.   Sorry that could have shortened/simplifed my question.

PS the line:

do v = 1, 1, 2, 3, 5, 8, 13, 21;

was just a starting hard coded point, just the example of what I wanted to soft code for my control list, nothing more...

Astounding
PROC Star

SAS arrays (in simplest of all possible terms) contain some of the variables within a single row of data.  This example is not the simplest of all possible terms, because this array is temporary.  That means it contains a set of constants rather than set of variables.

 

The intent of the DO loop here is to execute the "my work here" section 12 times.  The first time, since v=1, PARTICULAR_KEY is "2153*UGRD" or the first element in the set of constant terms.  The second time, since v=2, PARTICULAR_KEY is "2153*VETM" or the second element in the set of constant terms.  The value of V lets PARTICULAR_KEY take on a matching value from a set of possible values.  I'm not sure if that was your intent here, but that's how the code functions.

 

Whether this makes sense to do at all might depend on "my work here" and what work needs to be performed.  It's entirely possible that the approach needs to change 100%, depending on what goes into "my work here".

 

'

kjohnsonm
Lapis Lazuli | Level 10

Astounding,

Thanks for your help so far... 

 

Well I get up to the line %put &sqlobs.;   /*see below */

and my result of that line is 103 what i expect.

 

so here is my code:

proc sql;
create table TERM_TBL_DIST as
select DISTINCT 
STRM,
ACAD_CAREER,
datepart(term_begin_dt) format date9. as term_begin_d
from &snapshotlib..PS_TERM_TBL
WHERE ACAD_YEAR BETWEEN "&year_four." and "&year.";
quit;
proc sql;
create table TERM_TBL_DIST1 as
select DISTINCT 
Catx ('*',strm,acad_career,put(term_begin_d,date9.)) as KEY,
STRM,
ACAD_CAREER,
term_begin_d
from TERM_TBL_DIST;
quit;
proc print data = TERM_TBL_DIST1 noobs n;run;
proc sql noprint;
select DISTINCT  "'" || strip(key) || "'" into : key_list separated by ', '
from TERM_TBL_DIST1;
quit;
%put &key_list.;
%put &sqlobs.;

..up to that point.  I still am not sure what I am doing wrong with the rest of this code:

data a;
array keys {&sqlobs} $ 9 _temporary_ (&key_list);
do v=&key_list;
/*= 1 to {&sqlobs};*/   /* <-- here I tried doing a fixed loop with the now known size that didn't work for me either */
	current_key=keys{v};
	put current_key;
	strm=scan(&current_key.,1,'*');    
	acad_career=scan(&current_key.,2,'*');
	term_begin_d=scan(&current_key.,3,'*');
		/*  do inner loop here??   this part of the code will create records 1 for
each day 386 some days before the start of each term through to the last
day of the term... My inner do loop works as I want it to at this time, thus not snown again
*/
END;
drop v; RUN; proc print data=a;run;

-KJ

Astounding
PROC Star

Two items stand out.  If you have errors beyond that, it would be helpful to show the log.

 

First, it would be correct for the DO loop to go from 1 to 103.  You can use 1 to &sqlobs if you need flexibility for cases where 103 would not be the right number.

 

Second, there is no need to use macro language such as &CURRENT_KEY.  Just use CURRENT_KEY instead.  It's a character variable with the proper value.

 

That will get you further ... see if it's far enough. 

kjohnsonm
Lapis Lazuli | Level 10

Getting very close, it does not seem to either like that last field connactinated or my date format.

 

proc sql;
create table TERM_TBL_DIST as
select DISTINCT
STRM,
ACAD_CAREER,
datepart(term_begin_dt) format date9. as term_begin_d
from &snapshotlib..PS_TERM_TBL
WHERE ACAD_YEAR BETWEEN "&year_four." and "&year.";
quit;
proc sql;
create table TERM_TBL_DIST1 as
select DISTINCT
/*Catx ('*',strm,acad_career,put(term_begin_d,date9.),'-') as KEY,*/
Catx ('*',strm,acad_career,term_begin_d,'-') as KEY,
STRM,
ACAD_CAREER,
term_begin_d
from TERM_TBL_DIST;
quit;
proc print data = TERM_TBL_DIST1 noobs n;run;
proc sql noprint;
select DISTINCT  "'" || strip(key) || "'" into : key_list separated by ', '
from TERM_TBL_DIST1;
quit;
%put &key_list.;
%put &sqlobs.;

data a;
array keys {&sqlobs} $ 9 _temporary_ (&key_list);
do v= 1 to &sqlobs.;
    current_key=keys{v};
    strm=scan(&current_key.,1,'*');
    acad_career=scan(&current_key.,2,'*');
    term_begin_d=scan(&current_key.,3,'*');
    junk=scan(&current_key.,4,'*');   /* looking to see if it disliked the trailing field, seems to drop it and the date  */

        /*    do inner loop here??*/
    output;
/*    drop v;*/
END;
RUN;
proc print data=a noobs n;run;

it is dropping the date field before i go int my inner loop.

output sample:


                             current_             acad_      term_
                       v        key       strm    career    begin_d    junk

                      101    2175*PHAR    2175     PHAR
                      102    2175*UGRD    2175     UGRD
                      103    2175*VETM    2175     VETM

                                              N = 103

 

and no errors in log:

8035  proc sql;
8036  create table TERM_TBL_DIST as
8037  select DISTINCT
8038  STRM,
8039  ACAD_CAREER,
8040  datepart(term_begin_dt) format date9. as term_begin_d
8041  from &snapshotlib..PS_TERM_TBL
8042  WHERE ACAD_YEAR BETWEEN "&year_four." and "&year.";
NOTE: Table WORK.TERM_TBL_DIST created, with 103 rows and 3 columns.

8043  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.27 seconds
      cpu time            0.03 seconds


8044  proc sql;
8045  create table TERM_TBL_DIST1 as
8046  select DISTINCT
8047  /*Catx ('*',strm,acad_career,put(term_begin_d,date9.),'-') as KEY,*/
8048  Catx ('*',strm,acad_career,term_begin_d,'-') as KEY,
8049  STRM,
8050  ACAD_CAREER,
8051  term_begin_d
8052  from TERM_TBL_DIST;
NOTE: Table WORK.TERM_TBL_DIST1 created, with 103 rows and 4 columns.

8053  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds


8054  proc print data = TERM_TBL_DIST1 noobs n;run;

NOTE: There were 103 observations read from the data set WORK.TERM_TBL_DIST1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


8055  proc sql noprint;
8056  select DISTINCT  "'" || strip(key) || "'" into : key_list separated by ', '
8057  from TERM_TBL_DIST1;
8058  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


8059  %put &key_list.;
'2127*BUSN*19225*-', '2127*GRAD*19225*-', '2127*MEDI*19225*-', '2127*PHAR*19225*-',
'2127*UGRD*19225*-', '2127*VETM*19225*-', '2130*UGRD*19206*-', '2133*BUSN*19365*-',
'2133*GRAD*19365*-', '2133*MEDI*19365*-', '2133*PHAR*19365*-', '2133*UGRD*19365*-',
'2133*VETM*19365*-', '2135*BUSN*19484*-', '2135*GRAD*19484*-', '2135*MEDI*19484*-',
'2135*PHAR*19484*-', '2135*UGRD*19484*-', '2135*VETM*19484*-', '2137*BUSN*19589*-',
'2137*GRAD*19589*-', '2137*MEDI*19589*-', '2137*PHAR*19589*-', '2137*UGRD*19589*-',
'2137*VETM*19589*-', '2140*UGRD*-21549*-', '2143*BUSN*19736*-', '2143*GRAD*19736*-',
'2143*MEDI*19736*-', '2143*PHAR*19736*-', '2143*UGRD*19736*-', '2143*VETM*19736*-',
'2145*BUSN*19855*-', '2145*GRAD*19855*-', '2145*MEDI*19855*-', '2145*PHAR*19855*-',
'2145*UGRD*19855*-', '2145*VETM*19855*-', '2147*BUSN*19960*-', '2147*GRAD*19960*-',
'2147*MEDI*19960*-', '2147*PHAR*19960*-', '2147*UGRD*19960*-', '2147*VETM*19960*-',
'2150*UGRD*-21549*-', '2153*BUSN*20100*-', '2153*GRAD*20100*-', '2153*IALC*20100*-',
'2153*MEDI*20100*-', '2153*PHAR*20100*-', '2153*UGRD*20100*-', '2153*VETM*20100*-',
'2155*BUSN*20219*-', '2155*GRAD*20219*-', '2155*IALC*20219*-', '2155*MEDI*20219*-',
'2155*PHAR*20219*-', '2155*UGRD*20219*-', '2155*VETM*20219*-', '2157*BUSN*20324*-',
'2157*GRAD*20324*-', '2157*IALC*20324*-', '2157*MEDI*20324*-', '2157*PHAR*20324*-',
'2157*UGRD*20324*-', '2157*VETM*20324*-', '2160*UGRD*-21549*-', '2163*BUSN*20464*-',
'2163*GRAD*20464*-', '2163*IALC*20464*-', '2163*MEDI*20464*-', '2163*PHAR*20464*-',
'2163*UGRD*20464*-', '2163*VETM*20464*-', '2165*BUSN*20583*-', '2165*GRAD*20583*-',
'2165*IALC*20583*-', '2165*MEDI*20583*-', '2165*PHAR*20583*-', '2165*UGRD*20583*-',
'2165*VETM*20583*-', '2167*BUSN*20688*-', '2167*GRAD*20688*-', '2167*IALC*20688*-',
'2167*MEDI*20688*-', '2167*PHAR*20688*-', '2167*UGRD*20688*-', '2167*VETM*20688*-',
'2170*UGRD*-21549*-', '2173*BUSN*20828*-', '2173*GRAD*20828*-', '2173*IALC*20828*-',
'2173*MEDI*20828*-', '2173*PHAR*20828*-', '2173*UGRD*20828*-', '2173*VETM*20828*-',
'2175*BUSN*20947*-', '2175*GRAD*20947*-', '2175*IALC*20947*-', '2175*MEDI*20947*-',
'2175*PHAR*20947*-', '2175*UGRD*20947*-', '2175*VETM*20947*-'
8060  %put &sqlobs.;
103
8061
8062  data a;
8063  array keys {&sqlobs} $ 9 _temporary_ (&key_list);
8064  do v= 1 to &sqlobs.;
8065      current_key=keys{v};
8066      strm=scan(&current_key.,1,'*');
8067      acad_career=scan(&current_key.,2,'*');
8068      term_begin_d=scan(&current_key.,3,'*');
8069      junk=scan(&current_key.,4,'*');  
8070
8071          /*  do inner loop here??*/
8072      output;
8073  /*  drop v;*/
8074  END;
8075  RUN;

NOTE: The data set WORK.A has 103 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


8076  proc print data=a noobs n;run;

NOTE: There were 103 observations read from the data set WORK.A.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


Patrick
Opal | Level 21

@kjohnsonm

I believe what you're trying to do is not that hard as such BUT: It would really help if you could post some representative sample data (data steps creating such data) and describe the desired result as this would 1) remove the guess work for us and 2) allow us to post fully working and tested code.

Astounding
PROC Star

It looks like you considered another version when adding the date inside CATX.  put(term_begin_dt, date9.) might very well be correct but there's no way for me to tell.  You haven't shown what you are hoping to get as a result for that piece.

 

There are still references to &CURRENT_KEY, a macro variable that (at least as far as I can see) doesn't exist.  From what you have shown here, all instances of &CURRENT_KEY should remove the ampersand.

 

If arrays make you uncomfortable, take advantage of the fact that you have inserted commas between the items in &KEYLIST.  You could replace the three lines beginning with the ARRAY statment, and simply code:

 

do current_key = &keylist;

kjohnsonm
Lapis Lazuli | Level 10

Okay my latest version of this code with datalines.

What it seems to be doing is my key is dropping the date part, 'junk' was just a test to see if this code did not like to give the last item in a 'scan' command.

 

I expect to have a full "current_key with date" and term_begin_d and junk columns when done.

 

data TERM_TBL_DIST;
input	@01 STRM $4.
		@10 ACAD_CAREER $4.
		@19 term_begin_d date9.
		@33 junk $1.;
format	strm $4.
		acad_career $4.
		term_begin_d date9.
		junk $1.;

/*        ACAD_         term_
STRM    CAREER      begin_d    junk
*/

datalines;
2127     BUSN     20AUG2012     -
2127     GRAD     20AUG2012     -
2127     MEDI     20AUG2012     -
2127     PHAR     20AUG2012     -
2127     UGRD     20AUG2012     -
2127     VETM     20AUG2012     -
2130     UGRD     01AUG2012     -
2133     BUSN     07JAN2013     -
2133     GRAD     07JAN2013     -
2133     MEDI     07JAN2013     -
2133     PHAR     07JAN2013     -
2133     UGRD     07JAN2013     -
2133     VETM     07JAN2013     -
2135     BUSN     06MAY2013     -
2135     GRAD     06MAY2013     -
2135     MEDI     06MAY2013     -
2135     PHAR     06MAY2013     -
2135     UGRD     06MAY2013     -
2135     VETM     06MAY2013     -
2137     BUSN     19AUG2013     -
2137     GRAD     19AUG2013     -
2137     MEDI     19AUG2013     -
2137     PHAR     19AUG2013     -
2137     UGRD     19AUG2013     -
2137     VETM     19AUG2013     -
2140     UGRD     01JAN1901     -
2143     BUSN     13JAN2014     -
2143     GRAD     13JAN2014     -
2143     MEDI     13JAN2014     -
2143     PHAR     13JAN2014     -
2143     UGRD     13JAN2014     -
2143     VETM     13JAN2014     -
2145     BUSN     12MAY2014     -
2145     GRAD     12MAY2014     -
2145     MEDI     12MAY2014     -
2145     PHAR     12MAY2014     -
2145     UGRD     12MAY2014     -
2145     VETM     12MAY2014     -
2147     BUSN     25AUG2014     -
2147     GRAD     25AUG2014     -
2147     MEDI     25AUG2014     -
2147     PHAR     25AUG2014     -
2147     UGRD     25AUG2014     -
2147     VETM     25AUG2014     -
2150     UGRD     01JAN1901     -
2153     BUSN     12JAN2015     -
2153     GRAD     12JAN2015     -
2153     IALC     12JAN2015     -
2153     MEDI     12JAN2015     -
2153     PHAR     12JAN2015     -
2153     UGRD     12JAN2015     -
2153     VETM     12JAN2015     -
2155     BUSN     11MAY2015     -
2155     GRAD     11MAY2015     -
2155     IALC     11MAY2015     -
2155     MEDI     11MAY2015     -
2155     PHAR     11MAY2015     -
2155     UGRD     11MAY2015     -
2155     VETM     11MAY2015     -
2157     BUSN     24AUG2015     -
2157     GRAD     24AUG2015     -
2157     IALC     24AUG2015     -
2157     MEDI     24AUG2015     -
2157     PHAR     24AUG2015     -
2157     UGRD     24AUG2015     -
2157     VETM     24AUG2015     -
2160     UGRD     01JAN1901     -
2163     BUSN     11JAN2016     -
2163     GRAD     11JAN2016     -
2163     IALC     11JAN2016     -
2163     MEDI     11JAN2016     -
2163     PHAR     11JAN2016     -
2163     UGRD     11JAN2016     -
2163     VETM     11JAN2016     -
2165     BUSN     09MAY2016     -
2165     GRAD     09MAY2016     -
2165     IALC     09MAY2016     -
2165     MEDI     09MAY2016     -
2165     PHAR     09MAY2016     -
2165     UGRD     09MAY2016     -
2165     VETM     09MAY2016     -
2167     BUSN     22AUG2016     -
2167     GRAD     22AUG2016     -
2167     IALC     22AUG2016     -
2167     MEDI     22AUG2016     -
2167     PHAR     22AUG2016     -
2167     UGRD     22AUG2016     -
2167     VETM     22AUG2016     -
2170     UGRD     01JAN1901     -
2173     BUSN     09JAN2017     -
2173     GRAD     09JAN2017     -
2173     IALC     09JAN2017     -
2173     MEDI     09JAN2017     -
2173     PHAR     09JAN2017     -
2173     UGRD     09JAN2017     -
2173     VETM     09JAN2017     -
2175     BUSN     08MAY2017     -
2175     GRAD     08MAY2017     -
2175     IALC     08MAY2017     -
2175     MEDI     08MAY2017     -
2175     PHAR     08MAY2017     -
2175     UGRD     08MAY2017     -
2175     VETM     08MAY2017     -
;
run;

proc sql;
create table TERM_TBL_DIST1 as
select DISTINCT 
"'" || STRM || "'" as STRM,
"'" || ACAD_CAREER || "'" as ACAD_CAREER,
compress("'" || put(term_begin_d,9.) || "'",,'s') as term_begin_d,
junk
from TERM_TBL_DIST;
quit;
proc print data = TERM_TBL_DIST1 noobs n;run;

proc sql;
create table TERM_TBL_DIST1 as
select DISTINCT 
STRM ||'*'|| ACAD_CAREER ||'*'|| compress(put(term_begin_d,9.) ,,'s')  ||'*'|| junk format $21. as key,
compress(put(term_begin_d,9.) ,,'s') format $9. as temp
from TERM_TBL_DIST;
quit;
proc print data = TERM_TBL_DIST1 noobs n;run;
proc contents data=TERM_TBL_DIST1;run;

proc sql noprint;
select DISTINCT  "'" || strip(key) || "'" into : key_list separated by ', '
from TERM_TBL_DIST1;
quit;
%put &key_list.;
%put &sqlobs.;

data a;
array keys {&sqlobs} $ 9 _temporary_ (&key_list);
do v= 1 to &sqlobs.;
	current_key=keys{v};
	strm=scan(current_key,1,'*');
	acad_career=scan(current_key,2,'*');
	term_begin_d=input(scan(current_key,3,'*'),date9.);
	junk=scan(current_key,4,'*');

		/*	do inner loop here??*/
	output;
	drop v;
END;
RUN;
proc print data=a noobs n;run;

 

 

 

my full log:

 

1792  data TERM_TBL_DIST;
1793  input   @01 STRM $4.
1794          @10 ACAD_CAREER $4.
1795          @19 term_begin_d date9.
1796          @33 junk $1.;
1797  format  strm $4.
1798          acad_career $4.
1799          term_begin_d date9.
1800          junk $1.;
1801
1802  /*        ACAD_         term_
1803  STRM    CAREER      begin_d    junk
1804  */
1805
1806  datalines;

NOTE: The data set WORK.TERM_TBL_DIST has 103 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


1910  ;
1911  run;
1912
1913  proc sql;
1914  create table TERM_TBL_DIST1 as
1915  select DISTINCT
1916  "'" || STRM || "'" as STRM,
1917  "'" || ACAD_CAREER || "'" as ACAD_CAREER,
1918  compress("'" || put(term_begin_d,9.) || "'",,'s') as term_begin_d,
1919  junk
1920  from TERM_TBL_DIST;
NOTE: Table WORK.TERM_TBL_DIST1 created, with 103 rows and 4 columns.

1921  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


1922  proc print data = TERM_TBL_DIST1 noobs n;run;

NOTE: There were 103 observations read from the data set WORK.TERM_TBL_DIST1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


1923
1924  proc sql;
1925  create table TERM_TBL_DIST1 as
1926  select DISTINCT
1927  STRM ||'*'|| ACAD_CAREER ||'*'|| compress(put(term_begin_d,9.) ,,'s')  ||'*'|| junk format
1927! $21. as key,
1928  compress(put(term_begin_d,9.) ,,'s') format $9. as temp
1929  from TERM_TBL_DIST;
NOTE: Table WORK.TERM_TBL_DIST1 created, with 103 rows and 2 columns.

1930  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


1931  proc print data = TERM_TBL_DIST1 noobs n;run;

NOTE: There were 103 observations read from the data set WORK.TERM_TBL_DIST1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


1932  proc contents data=TERM_TBL_DIST1;run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


1933
1934  proc sql noprint;
1935  select DISTINCT  "'" || strip(key) || "'" into : key_list separated by ', '
1936  from TERM_TBL_DIST1;
1937  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


1938  %put &key_list.;
'2127*BUSN*19225*-', '2127*GRAD*19225*-', '2127*MEDI*19225*-', '2127*PHAR*19225*-',
'2127*UGRD*19225*-', '2127*VETM*19225*-', '2130*UGRD*19206*-', '2133*BUSN*19365*-',
'2133*GRAD*19365*-', '2133*MEDI*19365*-', '2133*PHAR*19365*-', '2133*UGRD*19365*-',
'2133*VETM*19365*-', '2135*BUSN*19484*-', '2135*GRAD*19484*-', '2135*MEDI*19484*-',
'2135*PHAR*19484*-', '2135*UGRD*19484*-', '2135*VETM*19484*-', '2137*BUSN*19589*-',
'2137*GRAD*19589*-', '2137*MEDI*19589*-', '2137*PHAR*19589*-', '2137*UGRD*19589*-',
'2137*VETM*19589*-', '2140*UGRD*-21549*-', '2143*BUSN*19736*-', '2143*GRAD*19736*-',
'2143*MEDI*19736*-', '2143*PHAR*19736*-', '2143*UGRD*19736*-', '2143*VETM*19736*-',
'2145*BUSN*19855*-', '2145*GRAD*19855*-', '2145*MEDI*19855*-', '2145*PHAR*19855*-',
'2145*UGRD*19855*-', '2145*VETM*19855*-', '2147*BUSN*19960*-', '2147*GRAD*19960*-',
'2147*MEDI*19960*-', '2147*PHAR*19960*-', '2147*UGRD*19960*-', '2147*VETM*19960*-',
'2150*UGRD*-21549*-', '2153*BUSN*20100*-', '2153*GRAD*20100*-', '2153*IALC*20100*-',
'2153*MEDI*20100*-', '2153*PHAR*20100*-', '2153*UGRD*20100*-', '2153*VETM*20100*-',
'2155*BUSN*20219*-', '2155*GRAD*20219*-', '2155*IALC*20219*-', '2155*MEDI*20219*-',
'2155*PHAR*20219*-', '2155*UGRD*20219*-', '2155*VETM*20219*-', '2157*BUSN*20324*-',
'2157*GRAD*20324*-', '2157*IALC*20324*-', '2157*MEDI*20324*-', '2157*PHAR*20324*-',
'2157*UGRD*20324*-', '2157*VETM*20324*-', '2160*UGRD*-21549*-', '2163*BUSN*20464*-',
'2163*GRAD*20464*-', '2163*IALC*20464*-', '2163*MEDI*20464*-', '2163*PHAR*20464*-',
'2163*UGRD*20464*-', '2163*VETM*20464*-', '2165*BUSN*20583*-', '2165*GRAD*20583*-',
'2165*IALC*20583*-', '2165*MEDI*20583*-', '2165*PHAR*20583*-', '2165*UGRD*20583*-',
'2165*VETM*20583*-', '2167*BUSN*20688*-', '2167*GRAD*20688*-', '2167*IALC*20688*-',
'2167*MEDI*20688*-', '2167*PHAR*20688*-', '2167*UGRD*20688*-', '2167*VETM*20688*-',
'2170*UGRD*-21549*-', '2173*BUSN*20828*-', '2173*GRAD*20828*-', '2173*IALC*20828*-',
'2173*MEDI*20828*-', '2173*PHAR*20828*-', '2173*UGRD*20828*-', '2173*VETM*20828*-',
'2175*BUSN*20947*-', '2175*GRAD*20947*-', '2175*IALC*20947*-', '2175*MEDI*20947*-',
'2175*PHAR*20947*-', '2175*UGRD*20947*-', '2175*VETM*20947*-'
1939  %put &sqlobs.;
103
1940
1941  data a;
1942  array keys {&sqlobs} $ 9 _temporary_ (&key_list);
1943  do v= 1 to &sqlobs.;
1944      current_key=keys{v};
1945      strm=scan(current_key,1,'*');
1946      acad_career=scan(current_key,2,'*');
1947      term_begin_d=input(scan(current_key,3,'*'),date9.);
1948      junk=scan(current_key,4,'*');
1949
1950          /*  do inner loop here??*/
1951      output;
1952      drop v;
1953  END;
1954  RUN;

NOTE: The data set WORK.A has 103 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


1955  proc print data=a noobs n;run;

NOTE: There were 103 observations read from the data set WORK.A.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

 

my full output:


                                          The SAS System         08:20 Friday, January 6, 2017 174

                                         ACAD_      term_
                                STRM     CAREER    begin_d     junk

                               '2127'    'BUSN'    '19225'      -
                               '2127'    'GRAD'    '19225'      -
                               '2127'    'MEDI'    '19225'      -
                               '2127'    'PHAR'    '19225'      -
                               '2127'    'UGRD'    '19225'      -
                               '2127'    'VETM'    '19225'      -
                               '2130'    'UGRD'    '19206'      -
                               '2133'    'BUSN'    '19365'      -
                               '2133'    'GRAD'    '19365'      -
                               '2133'    'MEDI'    '19365'      -
                               '2133'    'PHAR'    '19365'      -
                               '2133'    'UGRD'    '19365'      -
                               '2133'    'VETM'    '19365'      -
                               '2135'    'BUSN'    '19484'      -
                               '2135'    'GRAD'    '19484'      -
                               '2135'    'MEDI'    '19484'      -
                               '2135'    'PHAR'    '19484'      -
                               '2135'    'UGRD'    '19484'      -
                               '2135'    'VETM'    '19484'      -
                               '2137'    'BUSN'    '19589'      -
                               '2137'    'GRAD'    '19589'      -
                               '2137'    'MEDI'    '19589'      -
                               '2137'    'PHAR'    '19589'      -
                               '2137'    'UGRD'    '19589'      -
                               '2137'    'VETM'    '19589'      -
                               '2140'    'UGRD'    '-21549'     -
                               '2143'    'BUSN'    '19736'      -
                               '2143'    'GRAD'    '19736'      -
                               '2143'    'MEDI'    '19736'      -
                               '2143'    'PHAR'    '19736'      -
                               '2143'    'UGRD'    '19736'      -
                               '2143'    'VETM'    '19736'      -
                               '2145'    'BUSN'    '19855'      -
                               '2145'    'GRAD'    '19855'      -
                               '2145'    'MEDI'    '19855'      -
                               '2145'    'PHAR'    '19855'      -
                               '2145'    'UGRD'    '19855'      -
                               '2145'    'VETM'    '19855'      -
                               '2147'    'BUSN'    '19960'      -
                               '2147'    'GRAD'    '19960'      -
                               '2147'    'MEDI'    '19960'      -
                               '2147'    'PHAR'    '19960'      -
                               '2147'    'UGRD'    '19960'      -
                               '2147'    'VETM'    '19960'      -
                               '2150'    'UGRD'    '-21549'     -
                               '2153'    'BUSN'    '20100'      -
                               '2153'    'GRAD'    '20100'      -
                               '2153'    'IALC'    '20100'      -
                               '2153'    'MEDI'    '20100'      -
                               '2153'    'PHAR'    '20100'      -

                                          The SAS System         08:20 Friday, January 6, 2017 175

                                         ACAD_      term_
                                STRM     CAREER    begin_d     junk

                               '2153'    'UGRD'    '20100'      -
                               '2153'    'VETM'    '20100'      -
                               '2155'    'BUSN'    '20219'      -
                               '2155'    'GRAD'    '20219'      -
                               '2155'    'IALC'    '20219'      -
                               '2155'    'MEDI'    '20219'      -
                               '2155'    'PHAR'    '20219'      -
                               '2155'    'UGRD'    '20219'      -
                               '2155'    'VETM'    '20219'      -
                               '2157'    'BUSN'    '20324'      -
                               '2157'    'GRAD'    '20324'      -
                               '2157'    'IALC'    '20324'      -
                               '2157'    'MEDI'    '20324'      -
                               '2157'    'PHAR'    '20324'      -
                               '2157'    'UGRD'    '20324'      -
                               '2157'    'VETM'    '20324'      -
                               '2160'    'UGRD'    '-21549'     -
                               '2163'    'BUSN'    '20464'      -
                               '2163'    'GRAD'    '20464'      -
                               '2163'    'IALC'    '20464'      -
                               '2163'    'MEDI'    '20464'      -
                               '2163'    'PHAR'    '20464'      -
                               '2163'    'UGRD'    '20464'      -
                               '2163'    'VETM'    '20464'      -
                               '2165'    'BUSN'    '20583'      -
                               '2165'    'GRAD'    '20583'      -
                               '2165'    'IALC'    '20583'      -
                               '2165'    'MEDI'    '20583'      -
                               '2165'    'PHAR'    '20583'      -
                               '2165'    'UGRD'    '20583'      -
                               '2165'    'VETM'    '20583'      -
                               '2167'    'BUSN'    '20688'      -
                               '2167'    'GRAD'    '20688'      -
                               '2167'    'IALC'    '20688'      -
                               '2167'    'MEDI'    '20688'      -
                               '2167'    'PHAR'    '20688'      -
                               '2167'    'UGRD'    '20688'      -
                               '2167'    'VETM'    '20688'      -
                               '2170'    'UGRD'    '-21549'     -
                               '2173'    'BUSN'    '20828'      -
                               '2173'    'GRAD'    '20828'      -
                               '2173'    'IALC'    '20828'      -
                               '2173'    'MEDI'    '20828'      -
                               '2173'    'PHAR'    '20828'      -
                               '2173'    'UGRD'    '20828'      -
                               '2173'    'VETM'    '20828'      -
                               '2175'    'BUSN'    '20947'      -
                               '2175'    'GRAD'    '20947'      -
                               '2175'    'IALC'    '20947'      -
                               '2175'    'MEDI'    '20947'      -

                                          The SAS System         08:20 Friday, January 6, 2017 176

                                         ACAD_      term_
                                STRM     CAREER    begin_d    junk

                               '2175'    'PHAR'    '20947'     -
                               '2175'    'UGRD'    '20947'     -
                               '2175'    'VETM'    '20947'     -

                                              N = 103

                                          The SAS System         08:20 Friday, January 6, 2017 177

                                key                      temp

                                2127*BUSN*19225*-        19225
                                2127*GRAD*19225*-        19225
                                2127*MEDI*19225*-        19225
                                2127*PHAR*19225*-        19225
                                2127*UGRD*19225*-        19225
                                2127*VETM*19225*-        19225
                                2130*UGRD*19206*-        19206
                                2133*BUSN*19365*-        19365
                                2133*GRAD*19365*-        19365
                                2133*MEDI*19365*-        19365
                                2133*PHAR*19365*-        19365
                                2133*UGRD*19365*-        19365
                                2133*VETM*19365*-        19365
                                2135*BUSN*19484*-        19484
                                2135*GRAD*19484*-        19484
                                2135*MEDI*19484*-        19484
                                2135*PHAR*19484*-        19484
                                2135*UGRD*19484*-        19484
                                2135*VETM*19484*-        19484
                                2137*BUSN*19589*-        19589
                                2137*GRAD*19589*-        19589
                                2137*MEDI*19589*-        19589
                                2137*PHAR*19589*-        19589
                                2137*UGRD*19589*-        19589
                                2137*VETM*19589*-        19589
                                2140*UGRD*-21549*-       -21549
                                2143*BUSN*19736*-        19736
                                2143*GRAD*19736*-        19736
                                2143*MEDI*19736*-        19736
                                2143*PHAR*19736*-        19736
                                2143*UGRD*19736*-        19736
                                2143*VETM*19736*-        19736
                                2145*BUSN*19855*-        19855
                                2145*GRAD*19855*-        19855
                                2145*MEDI*19855*-        19855
                                2145*PHAR*19855*-        19855
                                2145*UGRD*19855*-        19855
                                2145*VETM*19855*-        19855
                                2147*BUSN*19960*-        19960
                                2147*GRAD*19960*-        19960
                                2147*MEDI*19960*-        19960
                                2147*PHAR*19960*-        19960
                                2147*UGRD*19960*-        19960
                                2147*VETM*19960*-        19960
                                2150*UGRD*-21549*-       -21549
                                2153*BUSN*20100*-        20100
                                2153*GRAD*20100*-        20100
                                2153*IALC*20100*-        20100
                                2153*MEDI*20100*-        20100
                                2153*PHAR*20100*-        20100
                                2153*UGRD*20100*-        20100

                                          The SAS System         08:20 Friday, January 6, 2017 178

                                key                      temp

                                2153*VETM*20100*-        20100
                                2155*BUSN*20219*-        20219
                                2155*GRAD*20219*-        20219
                                2155*IALC*20219*-        20219
                                2155*MEDI*20219*-        20219
                                2155*PHAR*20219*-        20219
                                2155*UGRD*20219*-        20219
                                2155*VETM*20219*-        20219
                                2157*BUSN*20324*-        20324
                                2157*GRAD*20324*-        20324
                                2157*IALC*20324*-        20324
                                2157*MEDI*20324*-        20324
                                2157*PHAR*20324*-        20324
                                2157*UGRD*20324*-        20324
                                2157*VETM*20324*-        20324
                                2160*UGRD*-21549*-       -21549
                                2163*BUSN*20464*-        20464
                                2163*GRAD*20464*-        20464
                                2163*IALC*20464*-        20464
                                2163*MEDI*20464*-        20464
                                2163*PHAR*20464*-        20464
                                2163*UGRD*20464*-        20464
                                2163*VETM*20464*-        20464
                                2165*BUSN*20583*-        20583
                                2165*GRAD*20583*-        20583
                                2165*IALC*20583*-        20583
                                2165*MEDI*20583*-        20583
                                2165*PHAR*20583*-        20583
                                2165*UGRD*20583*-        20583
                                2165*VETM*20583*-        20583
                                2167*BUSN*20688*-        20688
                                2167*GRAD*20688*-        20688
                                2167*IALC*20688*-        20688
                                2167*MEDI*20688*-        20688
                                2167*PHAR*20688*-        20688
                                2167*UGRD*20688*-        20688
                                2167*VETM*20688*-        20688
                                2170*UGRD*-21549*-       -21549
                                2173*BUSN*20828*-        20828
                                2173*GRAD*20828*-        20828
                                2173*IALC*20828*-        20828
                                2173*MEDI*20828*-        20828
                                2173*PHAR*20828*-        20828
                                2173*UGRD*20828*-        20828
                                2173*VETM*20828*-        20828
                                2175*BUSN*20947*-        20947
                                2175*GRAD*20947*-        20947
                                2175*IALC*20947*-        20947
                                2175*MEDI*20947*-        20947
                                2175*PHAR*20947*-        20947
                                2175*UGRD*20947*-        20947

                                          The SAS System         08:20 Friday, January 6, 2017 179

                                key                      temp

                                2175*VETM*20947*-        20947

                                              N = 103

                                          The SAS System         08:20 Friday, January 6, 2017 180

                                      The CONTENTS Procedure

           Data Set Name        WORK.TERM_TBL_DIST1           Observations          103
           Member Type          DATA                          Variables             2
           Engine               V9                            Indexes               0
           Created              01/09/2017 13:36:23           Observation Length    30
           Last Modified        01/09/2017 13:36:23           Deleted Observations  0
           Protection                                         Compressed            NO
           Data Set Type                                      Sorted                YES
           Label
           Data Representation  WINDOWS_64
           Encoding             wlatin1  Western (Windows)


                                Engine/Host Dependent Information

Data Set Page Size          65536
Number of Data Set Pages    1
First Data Page             1
Max Obs per Page            2174
Obs in First Data Page      103
Number of Data Set Repairs  0
ExtendObsCounter            YES
Filename                    C:\Users\KJ\AppData\Local\Temp\SAS Temporary
                            Files\_TD22448_IR48_\term_tbl_dist1.sas7bdat
Release Created             9.0401M3
Host Created                X64_7PRO


                            Alphabetic List of Variables and Attributes

                              #    Variable    Type    Len    Format

                              1    key         Char     21    $21.
                              2    temp        Char      9    $9.


                                        Sort Information

                                     Sortedby       key temp
                                     Validated      YES
                                     Character Set  ANSI
                                     Sort Option    NODUPKEY

                                          The SAS System         08:20 Friday, January 6, 2017 181

                          current_             acad_      term_
                             key       strm    career    begin_d    junk

                          2127*BUSN    2127     BUSN        .
                          2127*GRAD    2127     GRAD        .
                          2127*MEDI    2127     MEDI        .
                          2127*PHAR    2127     PHAR        .
                          2127*UGRD    2127     UGRD        .
                          2127*VETM    2127     VETM        .
                          2130*UGRD    2130     UGRD        .
                          2133*BUSN    2133     BUSN        .
                          2133*GRAD    2133     GRAD        .
                          2133*MEDI    2133     MEDI        .
                          2133*PHAR    2133     PHAR        .
                          2133*UGRD    2133     UGRD        .
                          2133*VETM    2133     VETM        .
                          2135*BUSN    2135     BUSN        .
                          2135*GRAD    2135     GRAD        .
                          2135*MEDI    2135     MEDI        .
                          2135*PHAR    2135     PHAR        .
                          2135*UGRD    2135     UGRD        .
                          2135*VETM    2135     VETM        .
                          2137*BUSN    2137     BUSN        .
                          2137*GRAD    2137     GRAD        .
                          2137*MEDI    2137     MEDI        .
                          2137*PHAR    2137     PHAR        .
                          2137*UGRD    2137     UGRD        .
                          2137*VETM    2137     VETM        .
                          2140*UGRD    2140     UGRD        .
                          2143*BUSN    2143     BUSN        .
                          2143*GRAD    2143     GRAD        .
                          2143*MEDI    2143     MEDI        .
                          2143*PHAR    2143     PHAR        .
                          2143*UGRD    2143     UGRD        .
                          2143*VETM    2143     VETM        .
                          2145*BUSN    2145     BUSN        .
                          2145*GRAD    2145     GRAD        .
                          2145*MEDI    2145     MEDI        .
                          2145*PHAR    2145     PHAR        .
                          2145*UGRD    2145     UGRD        .
                          2145*VETM    2145     VETM        .
                          2147*BUSN    2147     BUSN        .
                          2147*GRAD    2147     GRAD        .
                          2147*MEDI    2147     MEDI        .
                          2147*PHAR    2147     PHAR        .
                          2147*UGRD    2147     UGRD        .
                          2147*VETM    2147     VETM        .
                          2150*UGRD    2150     UGRD        .
                          2153*BUSN    2153     BUSN        .
                          2153*GRAD    2153     GRAD        .
                          2153*IALC    2153     IALC        .
                          2153*MEDI    2153     MEDI        .
                          2153*PHAR    2153     PHAR        .

                                          The SAS System         08:20 Friday, January 6, 2017 182

                          current_             acad_      term_
                             key       strm    career    begin_d    junk

                          2153*UGRD    2153     UGRD        .
                          2153*VETM    2153     VETM        .
                          2155*BUSN    2155     BUSN        .
                          2155*GRAD    2155     GRAD        .
                          2155*IALC    2155     IALC        .
                          2155*MEDI    2155     MEDI        .
                          2155*PHAR    2155     PHAR        .
                          2155*UGRD    2155     UGRD        .
                          2155*VETM    2155     VETM        .
                          2157*BUSN    2157     BUSN        .
                          2157*GRAD    2157     GRAD        .
                          2157*IALC    2157     IALC        .
                          2157*MEDI    2157     MEDI        .
                          2157*PHAR    2157     PHAR        .
                          2157*UGRD    2157     UGRD        .
                          2157*VETM    2157     VETM        .
                          2160*UGRD    2160     UGRD        .
                          2163*BUSN    2163     BUSN        .
                          2163*GRAD    2163     GRAD        .
                          2163*IALC    2163     IALC        .
                          2163*MEDI    2163     MEDI        .
                          2163*PHAR    2163     PHAR        .
                          2163*UGRD    2163     UGRD        .
                          2163*VETM    2163     VETM        .
                          2165*BUSN    2165     BUSN        .
                          2165*GRAD    2165     GRAD        .
                          2165*IALC    2165     IALC        .
                          2165*MEDI    2165     MEDI        .
                          2165*PHAR    2165     PHAR        .
                          2165*UGRD    2165     UGRD        .
                          2165*VETM    2165     VETM        .
                          2167*BUSN    2167     BUSN        .
                          2167*GRAD    2167     GRAD        .
                          2167*IALC    2167     IALC        .
                          2167*MEDI    2167     MEDI        .
                          2167*PHAR    2167     PHAR        .
                          2167*UGRD    2167     UGRD        .
                          2167*VETM    2167     VETM        .
                          2170*UGRD    2170     UGRD        .
                          2173*BUSN    2173     BUSN        .
                          2173*GRAD    2173     GRAD        .
                          2173*IALC    2173     IALC        .
                          2173*MEDI    2173     MEDI        .
                          2173*PHAR    2173     PHAR        .
                          2173*UGRD    2173     UGRD        .
                          2173*VETM    2173     VETM        .
                          2175*BUSN    2175     BUSN        .
                          2175*GRAD    2175     GRAD        .
                          2175*IALC    2175     IALC        .
                          2175*MEDI    2175     MEDI        .

                                          The SAS System         08:20 Friday, January 6, 2017 183

                          current_             acad_      term_
                             key       strm    career    begin_d    junk

                          2175*PHAR    2175     PHAR        .
                          2175*UGRD    2175     UGRD        .
                          2175*VETM    2175     VETM        .

                                              N = 103

 

Astounding
PROC Star

While you have assigned a DATE9 format to your date variable, you did not use that format when forming your string:

 

compress("'" || put(term_begin_d,9.) || "'",,'s') as term_begin_d,

 

I think what you are looking for is this:

 

compress("'" || put(term_begin_d,date9.) || "'",,'s') as term_begin_d,

Patrick
Opal | Level 21

I still don't understand what your desired end result should be.

 

Your latest code version transposes your source data set from a long to a wide structure (held in a macro variable) only to then being transposed back from wide to long for the result table. That appears to me to be pretty useless. See below code who gives you the same result than what you've done so far.

data have;
  input @01 STRM $4.
    @10 ACAD_CAREER $4.
    @19 term_begin_d date9.
    ;
  format  strm $4.
    acad_career $4.
    term_begin_d date9.
    ;
  datalines;
2127     BUSN     20AUG2012     -
2127     GRAD     20AUG2012     -
2127     MEDI     20AUG2012     -
2127     PHAR     20AUG2012     -
2127     UGRD     20AUG2012     -
2127     VETM     20AUG2012     -
2130     UGRD     01AUG2012     -
2133     BUSN     07JAN2013     -
2133     GRAD     07JAN2013     -
2133     MEDI     07JAN2013     -
2133     PHAR     07JAN2013     -
2133     UGRD     07JAN2013     -
2133     VETM     07JAN2013     -
;
run;

data want;
  length current_key $9;
  set have;
  current_key=catx('*',strm,acad_career);
run;

Based on the sample data you've provided, can you explain how your desired result table should look like. You have some ""inner loop" comment in your code. What should happen in this inner loop and would you then want to write an output row per iteration over the inner loop?

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1401 views
  • 2 likes
  • 4 in conversation