DATA Step, Macro, Functions and more

Cannot transform imported char variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Cannot transform imported char variable

 

I am importing a csv file. After dropping the first lines with the metadata I am trying to transform the two columns into date and numeric formats respectively:

 

proc import datafile="/path/RealGDP2001-20016_Bulgaria.csv"
out=GDP_Bulgaria dbms=csv replace; 
getnames=no; 
run;

data GDP_Bulgaria; set GDP_Bulgaria; 
	if _n_>5; 
run;
                                                             

data GDP_Bulgaria; set GDP_Bulgaria; 
	date = input(var1, YYQ6.);
	format date yyq.;
	GDP = input(var2,8.);
	drop var1;
run;

No problems with the date, however for the numeric variable nothing seems to work. I tried compress(), trim(), strip() and vvalue() transformations before attempting to convert without success.

 

The problem seems to be related with the fact that some values have minus and other dont. If I change the variable length to 4 then those with minus are converted but the others aren't regardless if I am using compress or not.

Attachment

Accepted Solutions
Solution
‎02-18-2017 02:50 AM
Super User
Super User
Posts: 6,502

Re: Cannot transform imported char variable

[ Edited ]

Don't use PROC IMPORT to read a CSV file with only two columns. It will take less code to write the DATA step, plus you get to have complete control over the variable names and types.

 

data want ;
  infile cards dsd firstobs=6 truncover;
  length Date GDP 8 ;
  informat date yyq. ;
  format date yyq6. ;
  input Date GDP ;
cards4;
Data Source in SDW: http://sdw.ecb.europa.eu/quickview.do?SERIES_KEY=320.MNA.Q.Y.BG.W2.S1.S1.B.B1GQ._Z._Z._Z.XDC.LR.GY
,MNA.Q.Y.BG.W2.S1.S1.B.B1GQ._Z._Z._Z.XDC.LR.GY
",""Gross domestic product at market prices - Bulgaria - Domestic (home or reference area), Total economy, Domestic currency (incl. conversion to current currency made using a fix parity), Chain linked volume (rebased), Growth rate, over 1 year, Calendar a
Collection:,Summed through period (S)
Period\Unit:,
2016Q3,3.4
2016Q2,3.6
2016Q1,3.4
2015Q4,3.7
;;;;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,406

Re: Cannot transform imported char variable

Change GDP informat:

  

GDP = input(var2,best8.1);  

 

Occasional Contributor
Posts: 5

Re: Cannot transform imported char variable

I just tried but it doesn't work.

Super User
Posts: 5,099

Re: Cannot transform imported char variable

Your results are consistent with VAR2 being numeric originally.  All you would need to do is rename it to GDP.

PROC Star
Posts: 7,366

Re: Cannot transform imported char variable

Not sure what your problem is since your code does work for me. However, that said, here is a work around the should work:

 

proc import datafile="c:\art\RealGDP2001-20016_Bulgaria.csv"
out=GDP_Bulgaria dbms=csv replace; 
getnames=no;
datarow=6;
run;

data GDP_Bulgaria;
  set GDP_Bulgaria (rename=(var2=GDP));
  date = input(var1, YYQ6.);
  format date yyq.;
  drop var1;
run;

HTH,

Art, CEO, AnalystFinder.com

 

Solution
‎02-18-2017 02:50 AM
Super User
Super User
Posts: 6,502

Re: Cannot transform imported char variable

[ Edited ]

Don't use PROC IMPORT to read a CSV file with only two columns. It will take less code to write the DATA step, plus you get to have complete control over the variable names and types.

 

data want ;
  infile cards dsd firstobs=6 truncover;
  length Date GDP 8 ;
  informat date yyq. ;
  format date yyq6. ;
  input Date GDP ;
cards4;
Data Source in SDW: http://sdw.ecb.europa.eu/quickview.do?SERIES_KEY=320.MNA.Q.Y.BG.W2.S1.S1.B.B1GQ._Z._Z._Z.XDC.LR.GY
,MNA.Q.Y.BG.W2.S1.S1.B.B1GQ._Z._Z._Z.XDC.LR.GY
",""Gross domestic product at market prices - Bulgaria - Domestic (home or reference area), Total economy, Domestic currency (incl. conversion to current currency made using a fix parity), Chain linked volume (rebased), Growth rate, over 1 year, Calendar a
Collection:,Summed through period (S)
Period\Unit:,
2016Q3,3.4
2016Q2,3.6
2016Q1,3.4
2015Q4,3.7
;;;;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 137 views
  • 1 like
  • 5 in conversation