topic Re: Transpose Requires Conversion of transposed variable to text but need to export to Excel as numb in SAS Programming

Transpose Requires Conversion of transposed variable to text but need to export to Excel as number

texasmfp — Mon, 01 Sep 2025 09:53:02 GMT

I'm developing a program to use an API to call World Bank data for 10-year blocks. The data has one variable ("value") needs to be transposed to meet specific format. The ID for the transposition is the "date" field which is a 4-digit number specifying the year (2015, 2016, etc). SAS does not like using numbers as column names and the transposition leaves the column name with a "_" prefix. I used the VALIDVARNAME=ANY system option to remove the prefix. I am left with a 14-column database where the 10 rightmost columns are now numbers stored as characters (including the column name). The numbers include a mix of positive and negative values across a series (i.e., same row, across the 10 years) and some series have very large numbers while some series are small but have a large number of significant digits after the decimal point. I want to export those 10 columns to Excel as numbers, including the column name (i.e., a year) while retaining the 1st 4 columns as character fields. Seems like Proc Export can't change the formatting and, I will need to use ODS Excel and something called TAGATTR='format', which is new to me. I'll learn. But I'll throw in one more caveat: the column names, which again are years like 2015, 2016, etc... will change when I run the program for different 10-year time periods, so the variables that TAGATTR will reformat needs to be dynamic. Am I missing a simpler solution? If not, how can I create a dynamic TAGATTR formatting? Thanks

Re: Transpose Requires Conversion of transposed variable to text but need to export to Excel as numb

Tom — Mon, 01 Sep 2025 14:53:11 GMT

can you explain what you want to create it more detail? It would help if you showed an example input dataset and the output you want from it.

Also explain the reasons for any restrictions. For example why does it matter if the column headers in the EXCEL file are character strings instead of numbers?

Note if you are using the YEAR value as the indication of what variable to transpose into then you probably do NOT need to use PROC TRANSPOSE. You can just use a DATA STEP with an ARRAY that uses YEAR as the index. Something like this:

data wide;
   do until(last.id);
      set tall;
      by id;
      array m [2014:2025] y2014-y2025 ;
      if year in (2014:2025) then m[year] = value;
   end;
   drop year value ;
run;

And if you want an EXCEL file from your TALL dataset then you might not need to transpose it at all. Just use ODS EXCEL and PROC REPROT to make the file.

ods excel file='myfile.xslx';
proc report data=tall;
   column id value,year;
   define id / group;
   define year/ across ' ';
   define value / sum ' ';
run;
ods excel close;

Re: Transpose Requires Conversion of transposed variable to text but need to export to Excel as numb

Ksharp — Tue, 02 Sep 2025 07:21:38 GMT

The most simple solution is changing all these variables into CHARACTER type variables
or add TAB character before it. Like:
new_var=cats('09'x,var);

And it is better to post your example data and desired output.