To transpose table but retain the column length accordingly

[imdickson]

Hi SAS Community, Good day.

I have a table, which stores all columns attributes in row base, named as UDF_DEF table. In this table, it has column such as TABLE_NM, COLUMN_NM, COLUMN_DATA_TYPE, COLUMN_LENGTH.

So the sample data in table A would look like this:

TABLE_NM, COLUMN_NM, COLUMN_DATA_TYPE, COLUMN_LENGTH.
A          AGE              NUM               8
A          SEX               CHAR            20
A          LOCATION        CHAR            300
B          CAR BRAND      CHAR            100
C          NEWS BODY     LNGCHAR          1000

Sorry for the text alignment, but basically thats the data of this table.

But, when i do a proc transpose to make each of the row in COLUMN_NM become 1 column each, new columns are all 1000 length due to the fact that 'COLUMN_NM' in table A is 1000.

Is there a way that i could modify transpose function to give each of the columns a specific/individual length instead of the default 1000 ? What would the be method?

[RW9]

To what purpose, is this for reading data in from a text file, or altering an existing table?  The way to use it is slightly different depending on how you want to use it.  For instance, if it is to read a text file in, then you can use that dataset to generate the import code:

data _null_;
  set have;
  by table_nm;
  if first.table_nm then do;
    call execute(cats('filename tmp "c:\',table_nm,'";'));
    call execute('data '||strip(table_nm)||'; file tmp; length...');
  end;
  else do;
     ...
  end;
run;

If its to alter data already present then generate a proc datasets statment to alter.  This is where providing some test data (in the form of a datastep) and what the output should look like is so important, as I keep mentioning.