BookmarkSubscribeRSS Feed
ThomasH
Calcite | Level 5
Hello all,

got a little problem here and would be glad to get help.

Using a User Written Code in DI Studio I am trying to UNION 2 datasets to filter out duplicate rows.

Dataset a has 1 column with informat 12.7/outformat 12.7, so has dataset b.

Values are:
dataset a 12.1234594999
dataset b 12.1234595

The value in dataset a is displayed in DI Studio exactly as value b (due to informat/outformat), but once I do not use the usual table view of DI studio, it has all the decimals as written above. So once I do this...

SELECT * from a UNION SELECT * from b

...the values are identified as NOT duplicate.

So there are 2 questions:
- Why does the UNION neglect the in/outformat (do I need to specify the format in the UNION again?)
- How do I round this (round, roundz?) to fit value b so I can round it before the UNION

Any help is appreciated very much.

Thanks,
Thomas
2 REPLIES 2
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest you reply to your post with SAS log output showing code and resulting values of concern. Also, for your own investigation, a SAS CONTENTS listing may reveal differences in your two files - when SAS files are concatenated / merged, there are specific rules about how like-named SAS variables are treated when it comes to LENGTH, FORMAT, INFORMAT attributes.

Scott Barry
SBBWorks, Inc.
Patrick
Opal | Level 21
Hi Thomas

Forget about formats when it comes to comparisons. Formats are used only for WRITING data - but calculations/comparisons are always done with the full precision.

As this is user written code you can't expect DI Studio to do anything for you. And even if it wasn't user written: DI Studio does nothing else than generate SAS code - so look at the generated SAS code if you want to know what's going on.

Seems as rounding the values before the UNION would be a good idea.

Why not using a round() function in the select part of the SQL statement?

May be that could even be implemented in a standard SQL transform (I would have to try it but I expect it to be possible).

Just to show that round() works:
data a;
do i=1 to 5;
b=i;
output;
end;
run;

data b;
do i=2,3,5;
b=i*1.00002;
output;
end;
run;

proc sql;
select *
from a
union
select i,round(b)
from b
;
quit;

HTH
Patrick

P.S: I think it's round() you need. Have a look at the SASOnlinedoc to get the exact syntax for the precision you need (it will be something like:
round(variable,0.0000001)

P.P.S: The question is if the values with lower precision are rounded or truncated. You would want to do the same to the values with higher precision.
If it's truncation: Use either a put/input combination or multiply by 10 power the number of digits behind the comma, use int() on the result, divide by 10 power the number of digits behind the comma.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 2 replies
  • 999 views
  • 0 likes
  • 3 in conversation