The origin of our quest from a lady SAS programer here where I work shared this code by email: proc sort data=sashelp.prdsale out=prdsale; by country region division year; run; quit; title; footnote; ods listing close; ods tagsets.excelxp file='c:\temp\flyover.xls' style=statistical; proc print data=prdsale noobs label split='*'; where country eq 'CANADA' and year eq 1993; id country region division; var prodtype product quarter month year; sum predict / style(header)={tagattr='format:Currency' flyover='Predicted Total'}; sum actual / style(column)={tagattr='format:Currency' flyover='Actual Total'}; label prodtype = 'Product*Type' predict = 'Predicted*Sales' actual = 'Actual*Sales'; run; quit; ods tagsets.excelxp close; ods listing; and it fully works for me...?  how about you?? PS I wish I had this code when I frist started this quest.   I could have had a working base sample to kick this party off... 8( ... View more

thanks, news to me... We all have keyboard macros pre-loaded when they start here from long ago... I can test it out and trim it down thanks agian. ... View more

I need/want to see the full string: "A student is retained in College if they switch from their first declared AIs or Major to another AI or Major within the College" ...in my flyover.   Given this code, save program as any name, this works on windows7 PC, program saved on network share, SAS9.4 M4.  it has minor sample data, open xls to see flyover.   data word_cleanup; input in2 3. out2 2.; datalines; 111 1 222 2 333 3 run; title1 ; title2 ; title3 ; title4 ; title5 ; title6 ; title7 ; title8 ; title9 ; title10 ; footnote1 ; footnote2 ; footnote3 ; footnote4 ; footnote5 ; footnote6 ; footnote7 ; footnote8 ;footnote9 ; footnote10 ; options mlogic mprint merror symbolgen ; * --- initialize variables ---; %Let FName = %SysGet( SAS_EXECFILEPATH ) ; %let sasfile= %sysget(SAS_EXECFILEname); %let odsfile=%qsubstr(&sasfile,1, %length(&sasfile)-4); %Let PName = %qsubstr(%sysget(SAS_EXECFILEPATH),1, %length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILEname))) ; %macro fdate(fmt); %global fdate; %global tdate; data _null_; call symput("fdate",left(put("&sysdate9"d,&fmt))); call symput("tdate",left(put(today(),&fmt))); run; %mend fdate; %fdate(date9.) ; %let outputfile=&odsfile.&tdate..xls; %let start_term_code=20013; %let last_term_code=20143; %let start_strm=2127; %let degree_start_strm=2123; %let latest_xw_strm=2153; %let highlight=cxf2dcdb; %let my_sheet=testing1; * --- output excel sheet for each program (retention plan descr) ---; %let outputfile=&odsfile._&tdate..xls; %put &outputfile; ods Tagsets.ExcelXP path = "&pname." file = "&outputfile." options ( Embedded_Titles='Yes' Embedded_Footnotes='Yes' zoom='100' ); ods Tagsets.ExcelXP options ( Sheet_Name="&my_sheet." Sheet_Interval='none' Absolute_Column_Width='7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7' Autofit_height='yes' skip_space='1,0,0,1,1' ) ; proc report data=word_cleanup nowd missing split='\' nocenter; column in2 out2; define in2 / group 'NO. of Students IN2' style(header)={tagattr="format:@" verticalalign=t flyover = 'A student is retained in College if they switch from their first declared AIs or Major to another AI or Major within the College'} ; define out2 / group 'NO. of Students OUT2' style(header)={tagattr="format:@" verticalalign=t flyover = 'A student is retained in College if they switch from their first declared AIs or Major to another AI or Major within the College'} ; RUN; ods tagsets.Excelxp close ; This is not the full program, just enuff to create a xls file with a few rows and a fly over...   I do not want to change my ods option: Absolute_Column_Width='7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7' to resolve this.   -thank you  -KJ ... View more

  1) We cannot share the source data. 2) We can read and are only asking for a better documentation site to use as a referance.   "What we want is a more detailed "referance site" to the flyover option for excel file output." does that not exist? ... View more

his code line: define grad_or_retain_in_prog_yr1 /analysis sum 'NO. of Students' style(header)={tagattr="format:@" verticalalign=t flyover='A student is considered Retained or Graduated in Program if their first declared primary program Academic Interest (AI) or Major remains unchanged from their first semester of enrollment at ZZZ.'} format=blank.; ... View more

To be clear: I am not asking about PDF, I am saying we borrowed the code from the PDF site thinking it might work for both...   What we want is a more detailed "referance site" to the flyover option for excel file output. ... View more

I need to pass the iner-loop the three pieces of data to control the loop that is all I am trying to do.  I think this is not a loop 'control' problem anymore this is a date formatting issue.   “I hate the way SAS uses dates.”  It’s easily the hardest language I have ever used ever with respect to using dates.    I am sorry for wasting your time. ... View more

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   ... View more

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 ... View more