How to Change Type and Format of a Variable Without Renaming?

[turcay]

Hello everyone,

I have one more simple question. I want to change the type and format of Yearmonth variable without changing its name in PROC SQL Actually, I was thinking that I  am writing the correct code however, nothing has changed. Can somebody help me to create my desired format for the variable YearMonth, please?

Data Have;
Input  ID YEARMONTH $;
Datalines;
1 201606
1 201607
1 201608
1 201609
1 201610
1 201611
1 201612
1 201701
1 201702
1 201703
1 201704
1 201705
;
Run;
PROC SQL;
CREATE TABLE WANT AS
SELECT *,Input(YEARMONTH,YYMMN6.) AS YEARMONTH Format=date9.
FROM Have;
QUIT;

Desired;

Thank you

[novinosrin]

You shouldn't use * and then name a var with the same name

PROC SQL;
CREATE TABLE WANT AS
SELECT id,Input(YEARMONTH,YYMMN6.) AS YEARMONTH Format=date9.
FROM Have;
QUIT;

Note:

1. Using *, you tell SQL processor to select all columns from the input table, which means the compiler has already got that to process.

2. Ideally, you should be using proc datasets to reassign formats rather than reading the file

3. If you want to just play with metadata, why bother reading the data portion 

[ChrisNZ]

Your code worked.

Did you notice the small icon at the top of the column?

You now have a SAS date and not a string.

One thing you must change: you have a same column name twice, which is of course impossible. Use:

select ID, input(YEARMONTH,yymmn6.) as YEARMONTH format=date9.

[turcay]

Thank you for your response.

But what if I have more than one variable. For example, what if I have 300 variables in my data set, should I write them one by one ?

[novinosrin]

in that case, renaming using dataset option is a good idea

PROC SQL;
CREATE TABLE WANT(drop=ym) AS
SELECT *,Input(ym,YYMMN6.) AS YEARMONTH Format=date9.
FROM Have(rename=(yearmonth=ym));
QUIT;