topic Convert text variable into numeric after traspose in New SAS User

Convert text variable into numeric after traspose

FRAFLUTE — Fri, 26 Jul 2019 09:14:30 GMT

Dear all! Thanks for the best support here!

I've a problem...

I have this type of table:

QT_APPENDICE Rata_Totale_Calcolata Incrocio

201257486                             279,46                            12 / 10000
201257486                             289,51                          12 / 15000
201257486                             299,57                          12 / 20000
201257486                           309,63                          12 / 25000
201257486                             319,68                          12 / 30000
201257486                             338,48                          12 / 35000
201257486                             357,24                          12 / 40000
201260661                             325,56                          12 / 10000
201260661                             343,80                          12 / 15000
201260661                             362,04                             12 / 20000
201260661                             380,28                           12 / 25000
201260661                             398,51                          12 / 30000
201260661                             416,75                             12 / 35000
201260661                             434,95                             12 / 40000

After the creation of this table i make a traspose where the column incrocio become the lable of the column. This is the code:

PROC SQL;
	CREATE VIEW WORK.SORTTempTableSorted1 AS
		SELECT T.Incrocio, T.Rata_Totale_Calcolata, T.QT_APPENDICE
	FROM WORK.QUERY_FOR_DB_MTX1_0000 as T
;
QUIT;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted1
	OUT=WORK.TRNSTransposed1(LABEL="WORK.QUERY_FOR_DB_MTX1_0000 trasposto")
	NAME=Origine
	LABEL=Etichetta
;
	BY QT_APPENDICE;
	VAR Incrocio Rata_Totale_Calcolata;
ID Incrocio;
IDLABEL Incrocio;
RUN;

The result is correct but now i need to filter the only for the value of rata totale (i filter the column origine with value "incrocio" created after the traspose) and i need to convert this character value in number. I create this code:

PROC SQL;
   CREATE TABLE WORK.MATRIX_OUT AS 
   SELECT t1.QT_APPENDICE,
((input(COMPRESS('12 / 10000'n),COMMAX10.2)))

PROC SQL;
   CREATE TABLE WORK.MATRIX_OUT AS 
   SELECT t1.QT_APPENDICE,
((input(COMPRESS('12 / 10000'n),COMMAX10.2))) FORMAT=COMMAX10.2,
((input(COMPRESS('12 / 15000'n),COMMAX10.2))) FORMAT=COMMAX10.2,
((input(COMPRESS('12 / 20000'n),COMMAX10.2))) FORMAT=COMMAX10.2,
((input(COMPRESS('12 / 25000'n),COMMAX10.2))) FORMAT=COMMAX10.2,
((input(COMPRESS('12 / 30000'n),COMMAX10.2))) FORMAT=COMMAX10.2,
((input(COMPRESS('12 / 35000'n),COMMAX10.2))) FORMAT=COMMAX10.2,
((input(COMPRESS('12 / 40000'n),COMMAX10.2))) FORMAT=COMMAX10.2
  FROM WORK.TRNSTRANSPOSED1 t1
      WHERE t1.Origine NOT = 'Incrocio';
QUIT;

with this code convert the text value in number, but it lost the label of column like the traspose and give the name "_TEMA001... _TEMA002... ecc".

If i use the AS attribute he rename the column.

How can i retain the label and convert the format with this code?

Thanks for the support!

Re: Convert text variable into numeric after traspose

rudfaden — Fri, 26 Jul 2019 11:07:40 GMT

Your need to give the columns you create a name

PROC SQL;
   CREATE TABLE WORK.MATRIX_OUT AS 
   SELECT t1.QT_APPENDICE,
((input(COMPRESS('12 / 10000'n),COMMAX10.2))) FORMAT=COMMAX10.2 as GiveMeAName1,
((input(COMPRESS('12 / 15000'n),COMMAX10.2))) FORMAT=COMMAX10.2 as GiveMeAName2,
((input(COMPRESS('12 / 20000'n),COMMAX10.2))) FORMAT=COMMAX10.2 as GiveMeAName3,
((input(COMPRESS('12 / 25000'n),COMMAX10.2))) FORMAT=COMMAX10.2 as GiveMeAName4,
((input(COMPRESS('12 / 30000'n),COMMAX10.2))) FORMAT=COMMAX10.2 as GiveMeAName5,
((input(COMPRESS('12 / 35000'n),COMMAX10.2))) FORMAT=COMMAX10.2 as GiveMeAName6,
((input(COMPRESS('12 / 40000'n),COMMAX10.2))) FORMAT=COMMAX10.2 as GiveMeAName7
  FROM WORK.TRNSTRANSPOSED1 t1
      WHERE t1.Origine NOT = 'Incrocio';
QUIT;

Re: Convert text variable into numeric after traspose

Tom — Fri, 26 Jul 2019 12:35:48 GMT

Convert the value to a number BEFORE transposing it.

PROC SQL;
  CREATE VIEW WORK.SORTTempTableSorted1 AS
    SELECT T.Incrocio
      , input(T.Rata_Totale_Calcolata,commaX32.) as Rata_Totale_Calcolata
      , T.QT_APPENDICE
    FROM WORK.QUERY_FOR_DB_MTX1_0000 as T
  ;
QUIT;

Make sure you know what it means before you use an INFORMAT with number specified for the decimal places. That tells SAS to treat strings that look like integers (do not have a character to indicate the decimal place) as if they had an implied decimal place. That is to divide the number being read by that power of 10. So '123456' would be converted by the informat 10.2 into the number 1,234.56 instead of 123,456.