turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- sql server vs SAS

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2014 02:25 PM

Hi Everyone,

I am just trying to convert following sql server syntax into SAS. I've used this in sql server but not sure what is the equivalent of this in SAS. I am sure someone can suggest me code for this one in SAS.

Here is description of this code:

I've a field "fiscal_year_month" which is numeric and has lenght either 6 or 7 digits. I need to take right 6 digits from this where it has 7 digits and save the results into fiscal_yearmo field.

case when len(fiscal_year_month) = 7 then RIGHT(fiscal_year_month,6) Else fiscal_year_month End as fiscal_yearmo.

Thanks in advance for your help.

Regards,

Accepted Solutions

Solution

01-22-2014
02:33 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to stat_sas

01-22-2014 02:33 PM

If you want the 6 least significant decimal digits of an integer just take the remainder when dividing by a million. In SAS that is the MOD() function.

data _null_;

input x;

y=mod(x,1000000);

put x= y=;

cards;

1234567

123456

run;

x=1234567 y=234567

x=123456 y=123456

All Replies

Solution

01-22-2014
02:33 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to stat_sas

01-22-2014 02:33 PM

If you want the 6 least significant decimal digits of an integer just take the remainder when dividing by a million. In SAS that is the MOD() function.

data _null_;

input x;

y=mod(x,1000000);

put x= y=;

cards;

1234567

123456

run;

x=1234567 y=234567

x=123456 y=123456

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2014 05:26 PM

Thanks so much Tom. It works but just need to understand login behind it. When number is 1234567 then remainder will be 234567 and I'll take it but when number is 123456 remainder will be

-876544. How can I develop my logic to tell SAS please?

Regards

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to stat_sas

01-22-2014 05:58 PM

How did you get a negative number? Do you want to get a negative value? That is not what the string functions you were using in SQL-SERVER will do. It is certainly not what the MOD() function will do.

Modulo is easiest to see with powers of ten as it just results it last n digits. So if you take mod(x,100) you will get the tens and hundreds place.

mod(1234,100) results in 34. mod(5,100) results in 5.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2014 06:24 PM

I'm guessing his input variable is actually text, not a number . . .

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to stat_sas

01-22-2014 03:31 PM

It depends on whether you are connecting to the SQL Server database (explicit SQL pass-through, or implicit pass-through via libname method), or coding against SAS datasets.

With explicit pass-through, you can pass the syntax directly to the SQL Server database for processing.

Implicit pass-through, or coding against SAS datasets, allows you to use the data step approach (as per Tom's example) or PROC SQL functionality -- in either case, you will likely be using SAS-specific functions.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to stat_sas

01-22-2014 06:04 PM

Not an efficient solution but a quick one.

data have;

input fiscal_year_month;

cards;

2011041

201205

201304

201404

201301

2014024

2013013

2013114

;

data want;

set have;

fiscal_yearmo=input(substr(put(fiscal_year_month, 8. -l), 1, 6), 8.);

run;