How to handle shift the row wise shift in observation's values in SAS dataset??

Ronin — Wed, 21 Nov 2018 06:02:35 GMT

Hi all,

Its my second day in life with SAS and I am struggling but learning a lot with this community's help. I am facing a situation here at work and hope will get some suitable solutions for you guys.

I had two datasets:

dataset 1 (with 86 variable and 7095 observation)-

Var 1	var 2	WEEK	var 4	STORE	var 6	var 7…….var 86
a	f	1	0	1	x	……………………
b	k	6	0	53	x	……………………
c	z	66	0	2	x	……………………
d	x	50	0	53	x	……………………
e	y	3	0	8	x	……………………
………….	………….	………….	………….	………….	………….	……………………
………….	………….	………….	………….	………….	………….	……………………

this will continue for all 7095 observations....

and dataset 2 (2 variables and 621 unique observations)

STORE	TIER
1	Tier A
2	Tier C
3	Tier B
8	Tier B
9	Tier C
53	Tier A
18	Tier C
98	Tier B

continued till 621 rows with 621 unique Store values....

I had modified the dataset 1 with by adding a numerically encoded new_variable with logic based on WEEK variable from dataset 1 and Tier column from dataset 2 and the output should come like:

Var 1	Var 2	WEEK	Var 4	STORE	Var 6	Var 7	var 8…………….var 85	var 86	new_variable
a	f	1	0	1	x	y	……………………………..	7 j	11
b	k	6	0	53	x	y	……………………………..	j u 6	11
c	z	66	0	2	x	y	……………………………..	next 7	23
d	x	50	0	53	x	y	……………………………..	j e s t k #2	11
e	y	3	0	8	x	y	……………………………..	l k j l_87	12
f	j	51	0	9	x	y	……………………………..	j k j_769	23
g	m	49	0	8	x	y	……………………………..	78_65_k j u	12

But unfortunately I am getting the new SAS dataset as:

	Var 1	Var 2	WEEK	Var 4	STORE	Var 6	Var 7	var 8…………….var 85	var 86	new_variable
	a	f	1	0	1	x	y	……………………………..	7 j
11
	b	k	6	0	53	x	y	……………………………..	j u 6
11
	c	z	66	0	2	x	y	……………………………..	next 7
23
	d	x	50	0	53	x	y	……………………………..	j e s t k #2
11
	e	y	3	0	8	x	y	……………………………..	l k j l_87
12
	f	j	51	0	9	x	y	……………………………..	j k j_769
23
	g	m	49	0	8	x	y	……………………………..	78_65_k j u
12

also when I converted the SAS file to CSV (.sas7bdat to .csv) then the output looks like:

Var 1	Var 2	WEEK	Var 4	STORE	Var 6	Var 7	var 8…………….var 85	var 86	new_variable
a	f	1	0	1	x	y	……………………………..	7 j
	11
b	k	6	0	53	x	y	……………………………..	j u 6
	11
c	z	66	0	2	x	y	……………………………..	next 7
	23
d	x	50	0	53	x	y	……………………………..	j e s t k #2
	11
e	y	3	0	8	x	y	……………………………..	l k j l_87
	12
f	j	51	0	9	x	y	……………………………..	j k j_769
	23
g	m	49	0	8	x	y	……………………………..	78_65_k j u
	12

I also got a warning message in log file which says:

WARNING: Multiple lengths were specified for the BY variable STORE by input data sets. This may cause unexpected results.

Can it be because of this. I am unable to solve it . Please help..

Re: How to handle shift the row wise shift in observation's values in SAS dataset??

gamotte — Wed, 21 Nov 2018 09:15:54 GMT

Hello,
Can you post the complete log for an execution of your program ?

Re: How to handle shift the row wise shift in observation's values in SAS dataset??

Ronin — Mon, 26 Nov 2018 04:51:50 GMT

hi sorry for delay was stuck with other project.

PFA the log file for one execution. Moreover I want to clarify something in actual data in place of Tier A, Tier B and Tier C its Tier 1, Tier 2 and Control and the threshold week 50 is coded as week=1968.

I have edited the distorted CSV file after the code execution...I found a issue that as per the code which you have given:

below logicis working well:

if WEEK le 50 then newvar=10;
    else newvar=20;

But, for all observations in the next logic:

if upcase(TIER)="TIER A" then newvar=newvar+1;
    else if  upcase(TIER)="TIER B" then newvar=newvar+2;
    else newvar=newvar+3;

the commad is directly moving to else statement resulting all the values of new variable as either 13 or 23 even for the place where it should be 11, 12, 21, 22

topic Re: How to handle shift the row wise shift in observation's values in SAS dataset?? in SAS Programming

How to handle shift the row wise shift in observation's values in SAS dataset??

Re: How to handle shift the row wise shift in observation's values in SAS dataset??

Re: How to handle shift the row wise shift in observation's values in SAS dataset??