Transposed data

[kbl]

Good afternoon,
I pulled some TAQ data, and the file is in a .CSV format. I've only kept (and am interested in ) about six variables. The data pulled just fine, but it is listed in a column, with all trades for one company listed, followed by all trades for the next company, etc. I need the file sorted so that i can see all companies that traded at a particular time (along with the number of shares traded). In essence, instead of having vertical data, I want horizontal data. Can you help? I am attaching one of the files so you can see what i mean. Thanks very much for any assistance you can provide.

[art297]

Can you attach an example showing how you want the resulting file to look?

Art, CEO, AnalystFinder.com

[kbl]

Time	A	AA	AAPL	other stocks…
	price	price
	price

Art,

Thanks for your reply.  The main shift I want is that I want each company' sym_Root  to be a separate column.  Time remains the first column.  Then column two will be the first company that trades at a given time with the price it traded at for that time.  The next column will be the second company and its trading price (if it traded at that time), etc.

[Astounding]

What you're asking for is a report.  The data should remain in vertical form.  A report might display the data in horizontal form, if that is useful to you.  It becomes a question of whether it is worth it to you to learn some programming skills, to be able to produce the report that you want.

[kbl]

Good afternoon,

I have programmed before, although it's been a couple of years since i used SAS. 

I need the tickers (SYM_ROOT) to be in a separate column for each stock with the price at which it is trading for a given time listed under the company ticker.  As a result, I don't think it is sufficient to create a report; I think I need to transpose the data.  Does that sound right?

[art297]

What you want to do is easy with proc transpose, but you have some duplicate entries for some companies at the same times. As long as the duplicates are irrelevant, you can get what you want with:

proc sort data=have nodupkey;
  by time_m sym_root;
run;

proc transpose data=have out=want (drop=_:);
  by time_m;
  id sym_root;
  var price;
run;

Art, CEO, AnalystFinder.com

[kbl]

Thanks so much Art!  This is exceptionally helpful.  Can I trouble you for one more?  I was going to do some other calculations in Excel, but I think it will be easier to use SAS for everything.

What changes do I need to make to the program if I want everything that you just sent, plus I want to keep the volume data by company.  So, after I have a column for each company's price data, I have a set of columns for each company's volume data?  I think there is not a duplicate issue; each time listed represents the last trade that minute for each company's stock, and we only need to sort to the minute (the first four digits), not to the second (the final digit in the time variable) .  The data can look something like this:

Time (end of each minute)

A

Stock2

Stock 3

Stock 4

Stock 5

…

Stock 500

A

Stock2

Stock 3

Stock 4

Stock 5

…

30:46

36.5

100

31:58

36.445

The last trade price for each stock each minute here

100

The corresponding trading volume

32:22

36.51

100

for each stock  for each minute here

33:55

36.55

We  have 500 stocks  in the index

200

34:36

36.575

and we  need end-of-minute price data here for only 5 days

100

35:57

36.57

so it should not be too large of a data file

100

36:50

36.75

100

37:58

36.77

100

38:43

36.76

100

[art297]

You can do it with a couple of proc transposes, but I always find the following macro easier to make a wide file (2 or more variable) wider: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

If you download and run that macro, here is all you then have to submit to get what you want:

proc sort data=have out=need nodupkey;
  by time_m sym_root;
run;

%transpose(data=need, out=want, by=time_m, id=sym_root,
delimiter=_,var=price size)

Art, CEO, AnalystFinder.com

[kbl]

Art,

Thanks for your help, and my apologies for the delayed response.  It took me awhile to get my data to read into the program.  That's done now, but I'm not following your comment about running the macro first and then add this code.  I went through the paper and the PowerPoint, but can you help with my next step?  Here is the program:

Filename three 'G:\threeone.dat';
                 data have;
                 infile  three expandtabs;
                 input TIME_M $ SYM_ROOT $ SIZE PRICE TIME2;
                 
                * out= need;
                 
                 
proc sort data=have out=need nodupkey;
  by time_m sym_root;
  prefix=,
                 var=,
                 autovars=,
                 id=,
                 descendingid=,
                 var_first=,
                 format=,
                 delimiter=,
                 copy=,
                 drop=,
                 sort=,
                 sort_options=,
                 use_varname=,
                 preloadfmt=,
                 guessingrows=,
                 newid=);
run;

%transpose(data=need, out=want, by=time_m, id=sym_root,
delimiter=_,var=price size)
run;

Thanks a lot.

[art297]

There was a link to the code on that page. The direct link to the code is: http://www.sascommunity.org/mwiki/images/b/be/BB-07-2013.sas

Copy and paste that code into whatever you use to submit SAS code. After the last line of the macro's code, paste the following lines :

Filename three 'G:\threeone.dat';
data have;
  infile  three expandtabs;
  input TIME_M $ SYM_ROOT $ SIZE PRICE TIME2;
run;
 
proc sort data=have out=need nodupkey;
  by time_m sym_root;
run;

%transpose(data=need, out=want, by=time_m, id=sym_root,
delimiter=_,var=price size)

Then click the run icon.

Art, CEO, AnalystFinder.com

[kbl]

Art,

Is all of this code included?  I ran this program, and did not get any errors, but I also did not get any output:

%macro transpose(libname_in=,
                 libname_out=,
                 data=,
                 out=,
                 by=,
                 prefix=,
                 var=,
                 autovars=,
                 id=,
                 descendingid=,
                 var_first=,
                 format=,
                 delimiter=,
                 copy=,
                 drop=,
                 sort=,
                 sort_options=,
                 use_varname=,
                 preloadfmt=,
                 guessingrows=,
                 newid=);

/*Check whether the data and out parameters contain one or two-level filenames*/
  %let lp=%sysfunc(findc(%superq(data),%str(%()));
  %if &lp. %then %do;
    %let rp=%sysfunc(findc(%superq(data),%str(%)),b));
    %let dsoptions=%qsysfunc(substrn(%nrstr(%superq(data)),&lp+1,&rp-&lp-1));
    %let data=%sysfunc(substrn(%nrstr(%superq(data)),1,%eval(&lp-1)));
  %end;
  %else %let dsoptions=;
  %if %sysfunc(countw(&data.)) eq 2 %then %do;
    %let libname_in=%scan(&data.,1);
    %let data=%scan(&data.,2);
  %end;
  %else %if %length(&libname_in.) eq 0 %then %do;
    %let libname_in=work;
  %end;

  %if %sysfunc(countw(&out.)) eq 2 %then %do;
    %let libname_out=%scan(&out.,1);
    %let out=%scan(&out.,2);
  %end;
  %else %if %length(&libname_out.) eq 0 %then %do;
    %let libname_out=work;
  %end;

  %if %length(&newid.) eq 0 %then %do;
    %let newid=row;
  %end;

  /*obtain last by variable*/
  %if %length(&by.) gt 0 %then %do;
    %let lastby=%scan(&by.,-1);
  %end;
  %else %do;
    %let lastby=;
  %end;

/*Create macro variable to contain a list of variables to be copied*/
 %let to_copy=;
  %if %length(&copy.) gt 0 %then %do;
    data t_e_m_p;
      set &libname_in..&data. (obs=1 keep=&copy.);
    run;

    proc sql noprint;
      select name
        into :to_copy separated by " "
          from dictionary.columns
            where libname="WORK" and
                  memname="T_E_M_P"
        ;
      quit;
  %end;

/*Populate var parameter in the event it has a null value*/
  %if %length(&var.) eq 0 %then %do;
    data t_e_m_p;
      set &libname_in..&data. (obs=1 drop=&by. &id. &copy.);
    run;

    proc sql noprint;
      select name
        into :var separated by " "
          from dictionary.columns
            where libname="WORK" and
                  memname="T_E_M_P"
        %if %sysfunc(upcase("&autovars.")) eq "CHAR" %then %do;
                  and type="char"
        %end;
        %else %if %sysfunc(upcase("&autovars.")) ne "ALL" %then %do;
                  and type="num"
        %end;
        ;
      quit;
  %end;
 
/*Initialize macro variables*/
  %let vars_char=;
  %let varlist_char=;
  %let vars_num=;
  %let varlist_num=;
  %let formats_char=;
  %let format_char=;
  %let formats_num=;
  %let format_num=;

/*Create file t_e_m_p to contain one record with all var variables*/
  data t_e_m_p;
    set &libname_in..&data. (obs=1 keep=&var.);
  run;

/*Create macro variables containing untransposed var names and formats*/
  proc sql noprint;
    select name, case
                   when missing(format) then " $"||strip(put(length,5.))||'.'
                   else strip(format)
                 end
      into :vars_char separated by " ",
           :formats_char separated by "~"
        from dictionary.columns
          where libname="WORK" and
                memname="T_E_M_P" and
                type="char"
    ;
    select name, case
                   when missing(format) then "best12."
                   else strip(format)
                 end
      into :vars_num separated by " ",
           :formats_num separated by "~"
        from dictionary.columns
          where libname="WORK" and
                memname="T_E_M_P" and
                type="num"
    ;
    select name
      into :vars_all separated by " "
        from dictionary.columns
          where libname="WORK" and
                memname="T_E_M_P"
    ;
  quit;

/*If sort parameter has a value of YES, create a sorted temporary data file*/
  %if %sysfunc(upcase("&sort.")) eq "YES" %then %do;
    %let notsorted=;
    proc sort data=&libname_in..&data.
                (
                 keep=&by. &id. &vars_char. &vars_num. &to_copy.
                 &dsoptions.
                )
                 out=t_e_m_p &sort_options. noequals;
      by &by.;
    run;
    %let data=t_e_m_p;
    %let libname_in=work;
  %end;
  %else %do;
    %let notsorted=notsorted;
  %end;

  /*if no id parameter is present, create one from &newid.*/
  %if %length(&id.) eq 0 %then %do;
    data t_e_m_p;
      set &libname_in..&data.;
      by &by.;
      if first.&lastby then &newid.=1;
      else &newid+1;
    run;
    %let id=&newid.;
    %let data=t_e_m_p;
    %let libname_in=work;
  %end;

/*Ensure guessingrows parameter contains a value*/
  %if %length(&guessingrows.) eq 0 %then %do;
    %let guessingrows=%sysfunc(constant(EXACTINT));
  %end;

/*Ensure a format is assigned to an id variable*/
  %if %length(&id.) gt 0 %then %do;
    proc sql noprint;
      select type,length,%sysfunc(strip(format))
        into :tr_macro_type, :tr_macro_len, :tr_macro_format
          from dictionary.columns
            where libname="%sysfunc(upcase(&libname_in.))" and
                  memname="%sysfunc(upcase(&data.))" and
                  upcase(name)="%sysfunc(upcase(&id.))"
        ;
    quit;

    %if %length(&format.) eq 0 %then %do;
      %let optsave=%sysfunc(getoption(missing),$quote.);
      options missing=.;
      %if %length(&tr_macro_format.) gt 0 %then %do;
        %let format=&tr_macro_format.;
      %end;
      %else %if "&tr_macro_type." eq "num " %then %do;
        %let format=%sysfunc(catt(best,&tr_macro_len.,%str(.)));
      %end;
      %else %do;
        %let format=%sysfunc(catt($,&tr_macro_len.,%str(.)));
      %end;
      options missing=&optsave;
    %end;
  %end;

/*Create macro variables containing ordered lists of the requested transposed variable
  names for character (varlist_char) and numeric (varlist_num) var variables */
  %if %length(&preloadfmt.) gt 0 %then %do;
    %if %sysfunc(countw(&preloadfmt.)) eq 1 %then %do;
      %let preloadfmt=&libname_in..&preloadfmt.;
    %end;
  %end;
  %else %do;
    %if %sysfunc(upcase("&sort.")) eq "YES" %then
     %let dsoptions=;
    proc freq data=&libname_in..&data. (obs=&guessingrows. keep=&id. &dsoptions.)
       noprint;
      tables &id./out=_for_format (keep=&id.);
    run;
    %if %sysfunc(upcase("&descendingid.")) eq "YES" %then %do;
      proc sort data=_for_format;
        by descending &id;
      run;
    %end;
    data _for_format;
      set _for_format;
      order=_n_;
    run;
  %end;

 proc sql noprint;
  %do i=1 %to 2;
    %if &i. eq 1 %then %let i_type=char;
    %else %let i_type=num;
    %if %length(&&vars_&i_type.) gt 0 %then %do;
    select distinct
      %do j=1 %to 2;
        %if &j. eq 1 %then %let j_type=;
        %else %let j_type=format;
        %do k=1 %to %sysfunc(countw(&&vars_&i_type.));
         "&j_type. "||cats("&prefix.",
          %if %sysfunc(upcase("&var_first.")) eq "NO" %then %do;
            put(&id.,&format),"&delimiter."
            %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
            ,scan("&&vars_&i_type.",&k.);
          %end;
          %else %do;
            %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
               scan("&&vars_&i_type.",&k.),;
            "&delimiter.",put(&id.,&format)
          %end;
          )
          %if &j. eq 2 %then
            ||" "||cats(scan("&&formats_&i_type.",&k.,"~"),";");
          %if &k. lt %sysfunc(countw(&&vars_&i_type.)) %then ||;
          %else ,;
        %end;
      %end;
      %if "&tr_macro_type." eq "num " %then &id. format=best12.;
        %else &id.;
        ,order
          into :varlist_&i_type. separated by " ",
               :format_&i_type. separated by " ",
               :idlist separated by " ",
               :idorder separated by " "
           %if %length(&preloadfmt.) gt 0 %then from &preloadfmt.;
           %else from _for_format;
               order by order
    ;
      %let num_numlabels=&sqlobs.;
    %end;
  %end;
  quit;

  proc sql noprint;
    select distinct
        %let j_type=;
        %do k=1 %to %sysfunc(countw(&&vars_all.));
      "&j_type. "||cats("&prefix.",
      
          %if %sysfunc(upcase("&var_first.")) eq "NO" %then %do;
          put(&id.,&format),"&delimiter.",
            %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
          scan("&&vars_all.",&k.);
          )
          %end;
          %else %do;
            %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
          scan("&&vars_all.",&k.),;
          "&delimiter.",put(&id.,&format))
          %end;
          %if &k. lt %sysfunc(countw(&&vars_all.)) %then ||;
          %else ,;
        %end;
        order
          into :varlist_all separated by " ",
               :idorder separated by " "
           %if %length(&preloadfmt.) gt 0 %then from &preloadfmt.;
           %else from _for_format;
               order by order
    ;
  quit;

/*Create a format that will be used to assign values to the transposed variables*/
  data _for_format;
    %if %length(&preloadfmt.) gt 0 %then set &preloadfmt. (rename=(&id.=start));
    %else set _for_format  (rename=(&id.=start));
    ;
    %if "&tr_macro_type." eq "num " %then retain fmtname "labelfmt" type "N";
    %else retain fmtname "$labelfmt" type "C";
    ;
    label=
     %if %length(&preloadfmt.) eq 0 %then _n_-1;
     %else order-1;
     ;
  run;

  proc format cntlin = _for_format;
  run ;

/*Create and run the datastep that does the transposition*/
  data &libname_out..&out.;
    set &libname_in..&data. (keep=&by. &id.
      %do i=1 %to %sysfunc(countw("&vars_char."));
        %scan(&vars_char.,&i.)
      %end;
      %do i=1 %to %sysfunc(countw("&vars_num."));
        %scan(&vars_num.,&i.)
      %end;
      %do i=1 %to %sysfunc(countw("&to_copy."));
        %scan(&to_copy.,&i.)
      %end;
      &dsoptions.
      );
    by &by. &notsorted.;
    &format_char. &format_num.
  %if %length(&vars_char.) gt 0 %then %do;
    array want_char(*) $
    %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_char.")));
      %scan(&varlist_char.,&i.)
    %end;
    ;
    array have_char(*) $ &vars_char.;
    retain want_char;
    if first.&lastby. then call missing(of want_char(*));
    ___nchar=put(&id.,labelfmt.)*dim(have_char);
    do ___i=1 to dim(have_char);
      want_char(___nchar+___i)=have_char(___i);
    end;
  %end;
  %if %length(&vars_num.) gt 0 %then %do;
    array want_num(*)
    %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_num.")));
      %scan(&varlist_num.,&i.)
    %end;
    ;
    array have_num(*) &vars_num.;
    retain want_num;
    if first.&lastby. then call missing(of want_num(*));
    ___nnum=put(&id.,labelfmt.)*dim(have_num);
    do ___i=1 to dim(have_num);
      want_num(___nnum+___i)=have_num(___i);
    end;
  %end;
    drop &id. ___: &var. &drop.;
    if last.&lastby. then output;
  run;

  data &libname_out..&out.;
    retain &by. &to_copy. &varlist_all.;
    set &libname_out..&out.;
  run;

/*Delete all temporary files*/
  proc delete data=work.t_e_m_p work._for_format;
  run;

%mend transpose;
options NOQUOTELENMAX;
proc sort data=have out=need nodupkey;
  by time_m sym_root;
run;

%transpose(data=need, out=want, by=time_m, id=sym_root,
delimiter=_,var=price size)

[art297]

Post your log. If the file HAVE was in your work directory, the file WANT should have been created in your work directory.

Art, CEO, AnalystFinder.com

[kbl]

Thanks a lot Art; this is the log:

972  %macro transpose(libname_in=,
973                   libname_out=,
974                   data=,
975                   out=,
976                   by=,
977                   prefix=,
978                   var=,
979                   autovars=,
980                   id=,
981                   descendingid=,
982                   var_first=,
983                   format=,
984                   delimiter=,
985                   copy=,
986                   drop=,
987                   sort=,
988                   sort_options=,
989                   use_varname=,
990                   preloadfmt=,
991                   guessingrows=,
992                   newid=);
993
994  /*Check whether the data and out parameters contain one or two-level filenames*/
995    %let lp=%sysfunc(findc(%superq(data),%str(%()));
996    %if &lp. %then %do;
997      %let rp=%sysfunc(findc(%superq(data),%str(%)),b));
998      %let dsoptions=%qsysfunc(substrn(%nrstr(%superq(data)),&lp+1,&rp-&lp-1));
999      %let data=%sysfunc(substrn(%nrstr(%superq(data)),1,%eval(&lp-1)));
1000    %end;
1001    %else %let dsoptions=;
1002    %if %sysfunc(countw(&data.)) eq 2 %then %do;
1003      %let libname_in=%scan(&data.,1);
1004      %let data=%scan(&data.,2);
1005    %end;
1006    %else %if %length(&libname_in.) eq 0 %then %do;
1007      %let libname_in=work;
1008    %end;
1009
1010    %if %sysfunc(countw(&out.)) eq 2 %then %do;
1011      %let libname_out=%scan(&out.,1);
1012      %let out=%scan(&out.,2);
1013    %end;
1014    %else %if %length(&libname_out.) eq 0 %then %do;
1015      %let libname_out=work;
1016    %end;
1017
1018    %if %length(&newid.) eq 0 %then %do;
1019      %let newid=row;
1020    %end;
1021
1022    /*obtain last by variable*/
1023    %if %length(&by.) gt 0 %then %do;
1024      %let lastby=%scan(&by.,-1);
1025    %end;
1026    %else %do;
1027      %let lastby=;
1028    %end;
1029
1030  /*Create macro variable to contain a list of variables to be copied*/
1031   %let to_copy=;
1032    %if %length(&copy.) gt 0 %then %do;
1033      data t_e_m_p;
1034        set &libname_in..&data. (obs=1 keep=&copy.);
1035      run;
1036
1037      proc sql noprint;
1038        select name
1039          into :to_copy separated by " "
1040            from dictionary.columns
1041              where libname="WORK" and
1042                    memname="T_E_M_P"
1043          ;
1044        quit;
1045    %end;
1046
1047  /*Populate var parameter in the event it has a null value*/
1048    %if %length(&var.) eq 0 %then %do;
1049      data t_e_m_p;
1050        set &libname_in..&data. (obs=1 drop=&by. &id. &copy.);
1051      run;
1052
1053      proc sql noprint;
1054        select name
1055          into :var separated by " "
1056            from dictionary.columns
1057              where libname="WORK" and
1058                    memname="T_E_M_P"
1059          %if %sysfunc(upcase("&autovars.")) eq "CHAR" %then %do;
1060                    and type="char"
1061          %end;
1062          %else %if %sysfunc(upcase("&autovars.")) ne "ALL" %then %do;
1063                    and type="num"
1064          %end;
1065          ;
1066        quit;
1067    %end;
1068
1069  /*Initialize macro variables*/
1070    %let vars_char=;
1071    %let varlist_char=;
1072    %let vars_num=;
1073    %let varlist_num=;
1074    %let formats_char=;
1075    %let format_char=;
1076    %let formats_num=;
1077    %let format_num=;
1078
1079  /*Create file t_e_m_p to contain one record with all var variables*/
1080    data t_e_m_p;
1081      set &libname_in..&data. (obs=1 keep=&var.);
1082    run;
1083
1084  /*Create macro variables containing untransposed var names and formats*/
1085    proc sql noprint;
1086      select name, case
1087                     when missing(format) then " $"||strip(put(length,5.))||'.'
1088                     else strip(format)
1089                   end
1090        into :vars_char separated by " ",
1091             :formats_char separated by "~"
1092          from dictionary.columns
1093            where libname="WORK" and
1094                  memname="T_E_M_P" and
1095                  type="char"
1096      ;
1097      select name, case
1098                     when missing(format) then "best12."
1099                     else strip(format)
1100                   end
1101        into :vars_num separated by " ",
1102             :formats_num separated by "~"
1103          from dictionary.columns
1104            where libname="WORK" and
1105                  memname="T_E_M_P" and
1106                  type="num"
1107      ;
1108      select name
1109        into :vars_all separated by " "
1110          from dictionary.columns
1111            where libname="WORK" and
1112                  memname="T_E_M_P"
1113      ;
1114    quit;
1115
1116  /*If sort parameter has a value of YES, create a sorted temporary data file*/
1117    %if %sysfunc(upcase("&sort.")) eq "YES" %then %do;
1118      %let notsorted=;
1119      proc sort data=&libname_in..&data.
1120                  (
1121                   keep=&by. &id. &vars_char. &vars_num. &to_copy.
1122                   &dsoptions.
1123                  )
1124                   out=t_e_m_p &sort_options. noequals;
1125        by &by.;
1126      run;
1127      %let data=t_e_m_p;
1128      %let libname_in=work;
1129    %end;
1130    %else %do;
1131      %let notsorted=notsorted;
1132    %end;
1133
1134    /*if no id parameter is present, create one from &newid.*/
1135    %if %length(&id.) eq 0 %then %do;
1136      data t_e_m_p;
1137        set &libname_in..&data.;
1138        by &by.;
1139        if first.&lastby then &newid.=1;
1140        else &newid+1;
1141      run;
1142      %let id=&newid.;
1143      %let data=t_e_m_p;
1144      %let libname_in=work;
1145    %end;
1146
1147  /*Ensure guessingrows parameter contains a value*/
1148    %if %length(&guessingrows.) eq 0 %then %do;
1149      %let guessingrows=%sysfunc(constant(EXACTINT));
1150    %end;
1151
1152  /*Ensure a format is assigned to an id variable*/
1153    %if %length(&id.) gt 0 %then %do;
1154      proc sql noprint;
1155        select type,length,%sysfunc(strip(format))
1156          into :tr_macro_type, :tr_macro_len, :tr_macro_format
1157            from dictionary.columns
1158              where libname="%sysfunc(upcase(&libname_in.))" and
1159                    memname="%sysfunc(upcase(&data.))" and
1160                    upcase(name)="%sysfunc(upcase(&id.))"
1161          ;
1162      quit;
1163
1164      %if %length(&format.) eq 0 %then %do;
1165        %let optsave=%sysfunc(getoption(missing),$quote.);
1166        options missing=.;
1167        %if %length(&tr_macro_format.) gt 0 %then %do;
1168          %let format=&tr_macro_format.;
1169        %end;
1170        %else %if "&tr_macro_type." eq "num " %then %do;
1171          %let format=%sysfunc(catt(best,&tr_macro_len.,%str(.)));
1172        %end;
1173        %else %do;
1174          %let format=%sysfunc(catt($,&tr_macro_len.,%str(.)));
1175        %end;
1176        options missing=&optsave;
1177      %end;
1178    %end;
1179
1180  /*Create macro variables containing ordered lists of the requested transposed variable
1181    names for character (varlist_char) and numeric (varlist_num) var variables */
1182    %if %length(&preloadfmt.) gt 0 %then %do;
1183      %if %sysfunc(countw(&preloadfmt.)) eq 1 %then %do;
1184        %let preloadfmt=&libname_in..&preloadfmt.;
1185      %end;
1186    %end;
1187    %else %do;
1188      %if %sysfunc(upcase("&sort.")) eq "YES" %then
1189       %let dsoptions=;
1190      proc freq data=&libname_in..&data. (obs=&guessingrows. keep=&id. &dsoptions.)
1191         noprint;
1192        tables &id./out=_for_format (keep=&id.);
1193      run;
1194      %if %sysfunc(upcase("&descendingid.")) eq "YES" %then %do;
1195        proc sort data=_for_format;
1196          by descending &id;
1197        run;
1198      %end;
1199      data _for_format;
1200        set _for_format;
1201        order=_n_;
1202      run;
1203    %end;
1204
1205   proc sql noprint;
1206    %do i=1 %to 2;
1207      %if &i. eq 1 %then %let i_type=char;
1208      %else %let i_type=num;
1209      %if %length(&&vars_&i_type.) gt 0 %then %do;
1210      select distinct
1211        %do j=1 %to 2;
1212          %if &j. eq 1 %then %let j_type=;
1213          %else %let j_type=format;
1214          %do k=1 %to %sysfunc(countw(&&vars_&i_type.));
1215           "&j_type. "||cats("&prefix.",
1216            %if %sysfunc(upcase("&var_first.")) eq "NO" %then %do;
1217              put(&id.,&format),"&delimiter."
1218              %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
1219              ,scan("&&vars_&i_type.",&k.);
1220            %end;
1221            %else %do;
1222              %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
1223                 scan("&&vars_&i_type.",&k.),;
1224              "&delimiter.",put(&id.,&format)
1225            %end;
1226            )
1227            %if &j. eq 2 %then
1228              ||" "||cats(scan("&&formats_&i_type.",&k.,"~"),";");
1229            %if &k. lt %sysfunc(countw(&&vars_&i_type.)) %then ||;
1230            %else ,;
1231          %end;
1232        %end;
1233        %if "&tr_macro_type." eq "num " %then &id. format=best12.;
1234          %else &id.;
1235          ,order
1236            into :varlist_&i_type. separated by " ",
1237                 :format_&i_type. separated by " ",
1238                 :idlist separated by " ",
1239                 :idorder separated by " "
1240             %if %length(&preloadfmt.) gt 0 %then from &preloadfmt.;
1241             %else from _for_format;
1242                 order by order
1243      ;
1244        %let num_numlabels=&sqlobs.;
1245      %end;
1246    %end;
1247    quit;
1248
1249    proc sql noprint;
1250      select distinct
1251          %let j_type=;
1252          %do k=1 %to %sysfunc(countw(&&vars_all.));
1253        "&j_type. "||cats("&prefix.",
1254
1255            %if %sysfunc(upcase("&var_first.")) eq "NO" %then %do;
1256            put(&id.,&format),"&delimiter.",
1257              %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
1258            scan("&&vars_all.",&k.);
1259            )
1260            %end;
1261            %else %do;
1262              %if %sysfunc(upcase("&use_varname.")) ne "NO" %then
1263            scan("&&vars_all.",&k.),;
1264            "&delimiter.",put(&id.,&format))
1265            %end;
1266            %if &k. lt %sysfunc(countw(&&vars_all.)) %then ||;
1267            %else ,;
1268          %end;
1269          order
1270            into :varlist_all separated by " ",
1271                 :idorder separated by " "
1272             %if %length(&preloadfmt.) gt 0 %then from &preloadfmt.;
1273             %else from _for_format;
1274                 order by order
1275      ;
1276    quit;
1277
1278  /*Create a format that will be used to assign values to the transposed variables*/
1279    data _for_format;
1280      %if %length(&preloadfmt.) gt 0 %then set &preloadfmt. (rename=(&id.=start));
1281      %else set _for_format  (rename=(&id.=start));
1282      ;
1283      %if "&tr_macro_type." eq "num " %then retain fmtname "labelfmt" type "N";
1284      %else retain fmtname "$labelfmt" type "C";
1285      ;
1286      label=
1287       %if %length(&preloadfmt.) eq 0 %then _n_-1;
1288       %else order-1;
1289       ;
1290    run;
1291
1292    proc format cntlin = _for_format;
1293    run ;
1294
1295  /*Create and run the datastep that does the transposition*/
1296    data &libname_out..&out.;
1297      set &libname_in..&data. (keep=&by. &id.
1298        %do i=1 %to %sysfunc(countw("&vars_char."));
1299          %scan(&vars_char.,&i.)
1300        %end;
1301        %do i=1 %to %sysfunc(countw("&vars_num."));
1302          %scan(&vars_num.,&i.)
1303        %end;
1304        %do i=1 %to %sysfunc(countw("&to_copy."));
1305          %scan(&to_copy.,&i.)
1306        %end;
1307        &dsoptions.
1308        );
1309      by &by. &notsorted.;
1310      &format_char. &format_num.
1311    %if %length(&vars_char.) gt 0 %then %do;
1312      array want_char(*) $
1313      %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_char.")));
1314        %scan(&varlist_char.,&i.)
1315      %end;
1316      ;
1317      array have_char(*) $ &vars_char.;
1318      retain want_char;
1319      if first.&lastby. then call missing(of want_char(*));
1320      ___nchar=put(&id.,labelfmt.)*dim(have_char);
1321      do ___i=1 to dim(have_char);
1322        want_char(___nchar+___i)=have_char(___i);
1323      end;
1324    %end;
1325    %if %length(&vars_num.) gt 0 %then %do;
1326      array want_num(*)
1327      %do i=1 %to %eval(&num_numlabels.*%sysfunc(countw("&vars_num.")));
1328        %scan(&varlist_num.,&i.)
1329      %end;
1330      ;
1331      array have_num(*) &vars_num.;
1332      retain want_num;
1333      if first.&lastby. then call missing(of want_num(*));
1334      ___nnum=put(&id.,labelfmt.)*dim(have_num);
1335      do ___i=1 to dim(have_num);
1336        want_num(___nnum+___i)=have_num(___i);
1337      end;
1338    %end;
1339      drop &id. ___: &var. &drop.;
1340      if last.&lastby. then output;
1341    run;
1342
1343    data &libname_out..&out.;
1344      retain &by. &to_copy. &varlist_all.;
1345      set &libname_out..&out.;
1346    run;
1347
1348  /*Delete all temporary files*/
1349    proc delete data=work.t_e_m_p work._for_format;
1350    run;
1351
1352  %mend transpose;
1353  options NOQUOTELENMAX;
1354  proc sort data=have out=need nodupkey;
1355    by time_m sym_root;
1356  run;
1357
1358  %transpose(data=need, out=want, by=time_m, id=sym_root,
1359  delimiter=_,var=price size)

[art297]

When your ran the code to first create the file have, did you check to see that it had actually run and created that file?

Honestly, I don't know what the problem might be! My guess is that something you ran prior to running the macro that caused SAS to stall. Close SAS and then restart it. Then run the same code as you ran before .. including first running your data step to create the file have.

Art, CEO, AnalystFinder.com

[kbl]

Well, you were right.  Once I shut down and restarted SAS, the file ran further.  And you were also correct: there is an issue with the work file. This is the log error; how do I correct this?

381  %mend transpose;
382  options NOQUOTELENMAX;
383  proc sort data=have out=need nodupkey;
ERROR: File WORK.HAVE.DATA does not exist.
384    by time_m sym_root;
385  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.NEED may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.06 seconds
      cpu time            0.03 seconds

386
387  %transpose(data=need, out=want, by=time_m, id=sym_root,
388  delimiter=_,var=price size)


ERROR: The variable price in the DROP, KEEP, or RENAME list has never been referenced.
ERROR: The variable size in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.T_E_M_P may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds