Resume

Infomart BI Solution

Location:

India

Salary:

69320.0

Posted:

January 14, 2020

Contact this candidate

Resume:

Looks like no one sent anything for this week – Anyways send below one for this week.

Dharam- Can you make this a weekly exercise where one person from offshore each week sends our relevant technical information on SAS to the whole team to improve SAS knowledge.

http://www.ats.ucla.edu/stat/sas/faq_histogramanno.htm

how can I calculate the week of the years from a SAS date?

You can simulate a WEEK fucntionby using the SAS functions INTCK and INTNK eq:-

DATA getweek;

datevvar =TODAY ;

week = INTCK(‘WEEK’, INTNK (‘YEAR’, datevar, 0)datevar)+1;

RUN;

SAS Tips for the week – Exploring Dictionary Tables in SAS.

Definition.

1) To display a tables definition.

You will be table see the DDL in the SAS loq.

PROC SQL;

DESCIBE TABLE DICTIONARY.OPTIONS;

QUIT;

2) To display Unique dictionary tables in the dictionary.

PROC SQL;

SELECT UNIQUE MEMNAME

FROM DICTIONARY.DICTIONARIES;

QUIT;

3) retrieving information about the columns in one or more data sets or tables.

PROC SQL;

SECENT *

FROM DICTIONARY.COLUMNS

WHERE UPCASE (LIBNAME )= ”DA_TEAM” AND UPCASE (NAME) = “MDS_CNTRL_VLDN_CE”;

QUIT;

4) retrieving information about the tables in one or more library.

PROC SQL;

SELECT *

FROM DICTIONARY.TABLES

WHERE UPCASE (LIBNAME) = “DA_TEMP”;

QIUT;

For more information visit.

http://support.sas.com

happy Learning

Handling Duplicates in SAS (Method-1)

Duplicates using Proc Sort Procedures.

The below code will delete duplicates at by variable SBSCRBR_ID MBR_SQNC_NBR YEAR_MNTH_NBR level and the DUPOUT option will send the Duplicates to a new dataset sot that you will come to which all observation are duplicate and got deleted from the original data set.

PROC SORT DATA =MCP_INDV_DS1CE NODUPKEY DUPOUT=MCP_DUFS_CE;

BY SBSCRBR_ID MBR_SQNC_NBR YEAR_MNTH_NBR;

RUN;

The below code will compares all variable values for each observation to those for the previous observation that was written to the output data set. If and exact match is found, then the duplicate observation is not written to the output data set.

PROC SORT DATA = MCP_INDV_DS1CE NODUP DUPOUT=MCP_DUFS_CE;

BY SBSCRBR_ID MBR_SQNC_NBR YEAR_MNTH_NBR;

RUN;

Happy Learning

Handling Duplicates in SAS (Method-2)

You can takes actions conditionally, based on whether you are processing the first or the last observation in a BY group.

Sample code:

SBSCRBR_ID

1) first sort the data set by the key variable or variables the key variable define unique observation.

proc sort data=SBSCRBR1 out=SBSCRBR1;

by SBSCRBR_ID;

run;

Displaying the first.Variable and last.Variables

data test;

set SBSCRBR1;

by SBSCRBR_ID;

first_SID=SBSCRBR_ID;

last_SID=SBSCRBR_ID;

run;

SBSCRBR_ID

First_SID

last_SID

2) below is the code that removes the duplicates from the SBSCRBR1 table.

data dupes;

set SBSCRBR1;

by SBSCRBR_ID;

if first.SBSCRBR_ID eq 1 then output;

run;

output

SBSCRBR_ID

Below is the cede that brings you the duplicate observations.

data dupes;

set SBSCRBR1;

by SBSCRBR_ID;

if (first.SBSCRBR_ID ne 1 or last.SBSCRBR_ID ne 1) then output;

run;

output

SBSCRBR_ID

For more information refer SAS online documentation

Happy Learning

SAS* SUBSTR function

Sample Data

DATA CITIZENS;

LENGTH NAME $20 DOB_CHAR $9 PREZ $4;

NAME = ”WASHINGTON, GEORGE”; DOB_CHAR = “22FEB1732”; PREZ = “YES”; OUTPUT;

NAME = “JEFFERSON, THOMAS”; DOB_CHAR = “13APR1743”; PREZ = “YES”; OUTPUT;

NAME = “FRANKLIN, BENJAMIC”; DOB_CHAR = “17JAN1706”; PREZ = “NOPE”; OUTPUT;

RUN;

name

dob_char

prez

WASHINGTON, GEORGE

22FEB1732

YES

JEFFERSON, THOMAS

13APR1743

Yes

FRANKLIN, BENJAMIN

17JAN1706

Nope

Example

DATA TEST;

SET CITIZENS;

IF UPCASE = “SUBSTR (PREZ,1,1)) = “Y” THEN TEXT_MSG” = ”WAS PRESIDENT OF THE USA”;

ELSE TEXT_MSG = “WAS NOT PRESIDENT OF THE USA”;

RUN;

OUTPUT;

name

dob_char

prez

TEXT_MSG

WASHINGTON, GEORGE

22FEB1732

YES

WAS PRESIDENT OF THE USA

JEFFERSON, THOMAS

13APR1743

Yes

WAS PRESIDENT OF THE USA

FRANKLIN, BENJAMIN

17JAN1706

Nope

WAS NOT PRESIDENT OF THE

Happy Learning

SAS* SUBSTR function.

Below is the sample code from one of the Adhoc request recently cane across.

**create a test data set.

data encrypt

(keep=SBSCRBR_ID MBR_SQNC_NBR MBR_UNIQ_ID SALES_WRTNG_AGENT_UNQ_ID SALES_PAID_AGENT_UNQ_ID SERV_WRTNG_AGENT_UNQ_ID SERV_PAID_AGENT_UNQ_ID);

set biz_vw.master_ne (abs=25);

run;

**Encrypt SBSCRBR_ID and created a new test variable SBSCRBR_ID_1;

data test1 (keep=SBSCRBR_ID SBSCRBR_ID_1);

lenqth SBSCRBR_ID_1 $20;

seed = 1235;

j=1;

lenq= lenqth (trim(left(decode)));

do while (j>0);

SBSCRBR_ID_1 = repeat lenq-1);

k =1;

do until (K>lenq);

x = int (1+(lenq)* ranuni (seed));

if x ne k or k eq lenq do;

if substr (SBSCRBR_ID_1,x,1) eq ‘-‘ then do;

substr (SBSCRBR_ID_1, x,1) = substr (decode,k,1);

k+1;

end;

if substr (SBSCRBR_ID_1, lenq, 1) eq substr (trim(left(revese(decode))),1,1) then do;

j+1;

seed+2;

end;

else do;

j=0;

end;

run;

output

SBSCRBR_ID_1

SBSCRBR_ID

LDQTNSNCAGREFMPBOIKJ

0000M49136

BFJISKCRGALMOTNQEPHD

0000M49203

BEFSIOMDNKCRLIQTHPCA

0000M49203

JHBQFPOIALDTEGMSRNKC

0000M49307

SANLOEHFJCTBQPMRKGID

0000M49311

FKNPHBAJSTRQLCEIGODM

0000M49339

CRQIGJPFSOATMBKECDNL

0000M49389

BCOHLQNJGFDMPRSATIKE

0000M49389

CGQKLEMOSTBIJRHFANPD

0000M49438

ORKILPDJGANETMBFHQCS

0000M49451

RHKBQCTMANSOLJFGPEID

0000M49474

DNFPSBJIHCRATKLQGMOE

0000M49476

TLARISHMDENGJKBQFCPO

0000M49480

GJDORITFLQMHNAECBSPK

0000M49482

GLDNBMCJHOSAPQTERFIK

0000M49483

RNOPLSJFETBIKCHGDMQA

0000M49484

FGBEORCQTNHIPADJSMLK

0000M49532

MSANKLQJEFBCDPIOGHTR

0000M49557

CTLRPEMNJIOQDSFAKHGB

0000M49566

HIFAQSMCTPNDRKEBLIGO

0000M49637

FJNKSGDPRTOQAMEILBHC

0000M49707

SQNJGKCALFOTPMDHBERI

0000M49754

MHEQNPRKDBFOAGISCTJL

0000M49762

PONHKAJMFTRQBGELCSDI

0000M49784

JMGLRESQBDINOPAHKFTC

0000M49842

Harry learning

Dealing with drop keep name rename option are statement.

1) the DROP=option tells SAS which variable you want to drop from a data set.

2) the KEEP=option tells SAS which variables you want to keep in a data set.

-Ex

data work.temp1 temp2 (drop=name);

set sashelp.class (keep=name sex aqe);

/*keep would pick up only these three fields in input dataset */

run;

data work.temp1 temp2;/*output datasets with all the fields except aqe*/

set sashelp.class (keep=name sex aqe);

drop aqe;

run;

3) the RENAME=data set options allows you to specify the variables you want to renames for each input or output data set.

-ex

data dataset1 dataset2;

rename aqe =new_aqe old_var=new_var;

set dataset2 (rename(temp1=temp2));/*For processing one has temp2 variable name.temp1 will no longer be available*/

run;

data dataset1 dataset2 (rename=(aqe=new_aqe old_var=new_var temp1=temp2));

/*option-variables will be renamed only in dataset2 and non in dataset*/

set dataset2;

run;

SAS arrays and Missing values.

Change all missing values of all variables into zeros / putting zeros in place of missing values for variables.

data missing;

input ID SCORE1 SCORE2 SCORE3 SCORe4 SCORE5 SCORE6;

cards;

24 100 97 . 100 85 85

28 . 87 98 100 . 90

60 100 . . 100 100 100

65 100 98 100 . 90 100

70-99-97-100-*** 95 100

40 97 99 98 . 100 95

190 100 . 97 100 100 90

196 100 100 . 100 100 100

210 . 85 . 90 80 95

;

Run;

data new;

set missing;

array zero score1-score6;

do over=. then zero = 0;

end;

output

SCORE1

SCORE2

SCORE3

SCORE4

SCORE5

SCORE6

100

190

100

196

100

210

Condition conversion of Missing values.

data new1;

set missing

array RS(6) score1-score6;

do i=1 to 6;

id RS(i)=. then RS(i)=1;

end;

else if id gt 70 then do;

if RS(i)=. then RS(i)=0;

end;

output

SCORE1

SCORE2

SCORE3

SCORE4

SCORE5

SCORE6

100

190

100

196

100

210

Happy Learning

COMBNING SAS DATE AND TIME FORMATS.

Background.

To combine two variables – a date and that are in SAS date and SAS time format You can convert the data to time for determine a SAS datetime values by multiplying the date by 24 hours, 60 minutes, and 60 seconds.

Code:

Use the following code:

data _null_;

format vardt date9. vartm time8. vardttm datetime16,;

vardt = “03MAR12”d;

vartm = “23:59:59”t;

vardttm = (vardt*24*60*60)+vartm;

put vardttm;

run;

Output

03MAR12:23:59:59

Another way to combine SAS date and time formats.

Here’s another way to convert a SAS date and SAS time to a SAS Datetime. Similar to the last Tip (below mail train) it will output the same thing without having to remembering to multiply the SAS date by 85,400 (or 24*60*60)

Code:

data _null_;

format vardt date9. vartm time8. vardttm datetime16.;

vardt = “07MAR12”d;

vartm = “22:22:22”t;

vardttm = input(put(vardt, date7.)) put(vartm, time8.).datetime16.);

put vardttm;

run;

output

07MAR12:22:22:22

SAS performance Tuning Tips.

1) Using the KEEP=data set option instructs the SAS System to load only the specified variables into the program data vector (PDV), eliminating all other variables from being loaded.

data class;

set sashelp.class (keep=name aqe sex);

if sex = “M”;

run;

2) the CLASS statement providers the ability to perform by group processing without the need for data to be sorted first in a separate step.

proc means data=sashelp.class;

class sex;

var heiqht weiqnt;

run;

3)By using IF-THEN / ELSE statements opposed to IF THEN statement without the ELSE, the SAS System stops processing the conditional logic once a condition holds true for any observation.

data class;

set sashelp.class;

if sex = “M” then Gender = “Male”;

else if sex = “F” then Gender = “Female”;

else Gender = “Other”;

run;

4) to avoid using default lengths for variables in a SAS dataset, use the LENGTH statement.

data class;

lenqth Name $8 Gender $6 Sex $1 aqe heiqht weiqht8;

set sashelp.class;

if sex = “M” then Gender = “Male”;

else if sex = “F” then Gender = “Female”;

else Gender = “Other”;

run;

5) to subset data without first running a DATA step use a WHERE statement in a procedure I/O and memory requirements may be deter for it.

proc print data = sashelp.class n noobs;

where sex = “M”;

title1 “SAS BI Programmers / Users”;

run;

6) Use the SQL procedure to simplify and consolidate coding requirements. CPU, I/0, and programming time may improve.

proc sql;

title1 “SAS BI Solutions Team Details”;

select * from sashelp.class

where sex = “M”

order by name;

quit;

7) to improve data storage and I/O requirements consider compressing large datasets.

data class (compress=yes);

set sashelp.class;

run;

Random Sampling

Sometime if is desirable to use just a subsample of your data in an analysis, and it is desired to extract sample, i.e. one in which each observation is just as likely to be included as each other observation RANUNI function.

Sample code:

The following program randomly extract the sample.

data sample;

set sashelp.class;

if ranuni *12345) <.1;

run;

Random Sampling

Sample code:

For example, to choose exactly 15 observations from a data set all, the following code could be used.

data sample;

retain k 15 n;

drop k n;

set da_team.master_feb_ny nobs=nn;

if _n_ =1 then n=nn;

if ranuni (0) k/n then do;

output;

k=k-1;

end;

if k=0 then stop;

n=n-1;

run;

Picture formats.

Displaying negative percent values with Negative sign and %sign.

Here is a picture formats that will do that.

proc format;

picture pctpic (round)

low -<0 =’009.99%’ (prefix mult = 10000)

0 –hiqh = ‘009.99%’ (mult=10000)

;

run;

data trnds_by_county;

set oh.trnds_by_county_mbr;

percent = (eoy_mbr_cnt_2010-eoy_mbr_cnt_2009)/eoy_mbr_cnt_2010;

format percent PCTPIC;

run;

to create the formats:-

1) we first have to convert the value to a person and that is what the MULT option does. If moves the decimal point over to the place where we want to display it.

2) then we need to shows the negative numbers with the negative sign and the PREFIZ option define this. So we need to define our format with two kinds of value – the negative and everything else. the picture itself shows the layout of the number including the deimal point and the percent siqn.

3) the 9’s indicates that we want a value displayed in that position, even if it is zero dropped. If we used ‘009.99%’, then a value of .59 would be displayed as 59%. We have also used the ROUND option to ensure that the value is rounched, not truncated, to our designated Z decimal places.

Original Data

STATE

CNTRCT_ST_FIPS_CNTY_NM

EOY_MBR_CNT_2009

EOY_MBR_CNT_2010

Percent

ADAMS

261

255

-0.0235294

ALLEN

812

781

-0.0396927

ASHLAND

425

409

-0.0391198

ASHTABULA

623

658

0.05319149

ATHENS

634

537

-0.1806331

AUGLAIZE

449

447

-0.0044743

With Picture format

STATE

CNTRCT_ST_FIPS_CNTY_NM

EOY_MBR_CNT_2009

EOY_MBR_CNT_2010

Percent

ADAMS

261

255

-2.35%

ALLEM

812

781

-3.97%

ASHLAND

425

409

-3.91%

ASHTABULA

623

658

5.32%

ATHENS

634

537

-18.06%

AUGLAIZE

449

447

(0.45%)

With Percentw.d. Format

STATE

CNTRCT_ST_FIPS_CNTY_NM

EOY_MBR_CNT_2009

EOY_MBR_CNT_2010

Percent

ADMS

261

255

(2.35%)

ALLEN

812

781

(3.97%)

ASHLAND

425

409

(3.91%)

ASHTABULA

623

658

5.32%

ATHENS

634

537

(18.06%)

AUGLAIZE

449

447

(0.45%)

If you are having performance issues and want to create a subset of a large data set just for testing, you can use the OBS=n data set option. The following example uses the OBS=10 option to reduce the use of system resources and will create subset of 10 observations. This will be very handy when you are dealing with large dataset like MDS. Once you are sure of your changes you run the original code without obs=option actives.

data U65_PRMTN_HSTRY_XTRCT_JUN;

set mrkt.U65_PRMTN_HSTRY_XTRCT_JUN (obs=10);

run;

Contact this candidate

Resume

Infomart BI Solution

Resume:

Similar Skills