BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gieorgie
Quartz | Level 8

I have a problem, I want to extract from the library exchange rates between two dates for EUR and USD, but I have to add PLN = 1 to calculate other values. But when I try to insert into it, it shows me an error

 

proc sql;
	create table kursy as
	select 
			DATA_DANYCH,
			CURRENCY_SHORT_NAME				as WALUTA,
			REVAL_RATEMID					as KURS
	from kondor.K_FXRATES
	where DATA_DANYCH between '24Apr2016'd and &gv_date_kon.
	  and CURRENCY_SHORT_NAME in ("EUR", "USD")
	order by DATA_DANYCH
;

	insert into kursy values('REVAL_RATEMID','PLN', 1)
;
quit;

DATA_DANYCH WALUTA KURS
2021-04-21 EUR 4,5567
2021-04-21 USD 4,4222
2021-04-21 PLN 1

Above table is what i expect :

Error is below:

50 insert into kursy values('REVAL_RATEMID','PLN', 1)
2 System SAS 14:38 Sunday, November 28, 2021

51 ;
ERROR: Value 1 of VALUES clause 1 does not match the data type of the corresponding column in the object-item list (in the SELECT
clause).

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

Drop insert, it won't work in your case, use a data step instead. Untested code:

data want;
  set kursy;
  by data_daynch;
  output;
  if last.data_danych then do;
    waluta = "PLN";
    kurs = 1;
    output;
  end;
run;

View solution in original post

12 REPLIES 12
PaigeMiller
Diamond | Level 26

Please do not show us error messages separated from the code. Please DO show us the entire log for this PROC SQL, every single line of the log for this PROC SQL, so we can see the code as it appears in the log; so we can see the NOTEs, WARNINGs and ERRORs as they appear in the log.

 

Please preserve the formatting of the log by pasting it as text into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

Insert Log Icon in SAS Communities.png

--
Paige Miller
Gieorgie
Quartz | Level 8
but when i tried put like this insert into kursy values('DATA_DANYCH',"PLN", 1)
i got same error
andreas_lds
Jade | Level 19

@Gieorgie wrote:
but when i tried put like this insert into kursy values('DATA_DANYCH',"PLN", 1)
i got same error

Rephrasing what Kurt already said: your first variable is a date, dates can't be set to a string-value.

Kurt_Bremser
Super User

Repeating the same action over and over and expecting a different result is a sure sign of insanity (Einstein).

You CANNOT insert a string into a numeric variable.

Gieorgie
Quartz | Level 8
Ok i ubderstand, but is there any chance to put PLN =1 to each date?
Kurt_Bremser
Super User
  1. Supply usable example data. Usable = a working data step with datalines, posted in a code box. This is essential to enable us to test our code suggestions and MUST NOT BE SKIPPED.
  2. Show what you expect to get out of this.
Gieorgie
Quartz | Level 8
im trying do from your data steps instruction %data2datastep(kursy,work,,5) but i got error :WARNING: Apparent invocation of macro DATA2DATASTEP not resolved.
27
28 %data2datastep(kursy,work,,5)
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
I dont know why
Gieorgie
Quartz | Level 8

Im used download macro and i get like this ;

28         %data2datastep(kursy,work,5)
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

DATA2DATASTEP DSN KURSY
DATA2DATASTEP FILE create_5_KURSY_data.sas
DATA2DATASTEP FMT YES
DATA2DATASTEP FMTLIST DATA_DANYCH YYMMDD10.
DATA2DATASTEP INPUTLIST DATA_DANYCH:YYMMDD10. WALUTA:$100. KURS:32.
DATA2DATASTEP LBL YES
DATA2DATASTEP LIB WORK
DATA2DATASTEP MEMLABEL 
DATA2DATASTEP MSGTYPE NOTE
DATA2DATASTEP OBS MAX
DATA2DATASTEP OUTLIB 5
DATA2DATASTEP VARLIST DATA_DANYCH WALUTA KURS

I have like here:

DATA_DANYCH WALUTA KURS
2021-04-21 EUR 4,5567
2021-04-21 USD 4,4222

and I would like to add PLN = 1 to each date

DATA_DANYCH WALUTA KURS
2021-04-21 EUR 4,5567
2021-04-21 USD 4,4222
2021-04-21 PLN 1
andreas_lds
Jade | Level 19

Drop insert, it won't work in your case, use a data step instead. Untested code:

data want;
  set kursy;
  by data_daynch;
  output;
  if last.data_danych then do;
    waluta = "PLN";
    kurs = 1;
    output;
  end;
run;
Tom
Super User Tom
Super User

So you seem to have clarified your request.

I would like to add PLN = 1 to each date

Why not just add them when you create the dataset?

create table kursy as

  select DATA_DANYCH
       , CURRENCY_SHORT_NAME as WALUTA
       , REVAL_RATEMID as KURS
  from kondor.K_FXRATES
  where DATA_DANYCH between '24Apr2016'd and &gv_date_kon.
    and CURRENCY_SHORT_NAME in ("EUR", "USD")

  union

  select distinct 
         DATA_DANYCH
       , 'PLN' as WALUTA
       , 1 as KURS
  from kondor.K_FXRATES
  where DATA_DANYCH between '24Apr2016'd and &gv_date_kon.
    and CURRENCY_SHORT_NAME in ("EUR", "USD")

  order by DATA_DANYCH
;

Now if you want to add a record for every date from '24Apr2016'd to &gv_date_kon rather than just those that appear in kondor.K_FXRATES then you might want a different approach.

data extra ;
  if 0 then set kursy ;
  WALUTA = 'PLN' ;
  KURS=1;
  do DATA_DANYCH =  '24Apr2016'd to  &gv_date_kon. ;
    output;
  end;
run;
proc append base=kursy data=extra;
run;

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 904 views
  • 4 likes
  • 5 in conversation