topic Re: How to perform similar vlookup function in dataflux and put conditions in SAS Data Management

How to perform similar vlookup function in dataflux and put conditions

Shah — Thu, 06 Aug 2015 09:18:04 GMT

Hi All,

I have two input files (1st file - contains 2000 records & 2nd File contains 5000 records) and both the files have ID, status_number as common, the percent field is in 2nd file. I have used concatenate funtion to club ID & status_number and named it as ID_Status to pull the percent field from 2nd file, but not receving accurate result. I am only interested in sales type.

To populate percent field in the 1st file
if duplicates exist in ID field then the condition must be
1. If ID is repeated twice then the percent should be 0 and 100
2. If ID is repeated thrice or more then it must be error

Screen shots are attached

1st File
SI No	ID	status_number	Type
1	123	45678	sales
2	123	34567	cost
3	123	78795	sales
4	123	98765	invoice
5	123	12345	sales
6	234	56872	sales
7	234	76549	cost
8	234	88887	sales
9	234	66666	invoice
10	234	11111	invoice

2nd File
SI No	ID	status_number	Type	Percent
1	123	45678	sales	0
2	123	34567	cost	100
3	123	78795	sales	100
4	123	98765	invoice	100
5	123	12345	sales	100
6	234	56872	sales	100
7	234	76549	cost	0
8	234	88887	sales	0
9	234	66666	invoice	100
10	234	11111	invoice	100

Output

SI No	ID	status_number	Type	Percent	Ouput
1	123	45678	sales	0	Error
3	123	78795	sales	100	Error
5	123	12345	sales	100	Error
6	234	56872	sales	100	Correct
8	234	88887	sales	0	Correct

Regards,

Shaheen

Re: How to perform similar vlookup function in dataflux and put conditions

Ksharp — Thu, 06 Aug 2015 12:40:11 GMT

Code: Program

data have;
infile cards expandtabs;
input SINo ID status_number Type $ Percent;
cards;
1 123 45678 sales 0
2 123 34567 cost 100
3 123 78795 sales 100
4 123 98765 invoice 100
5 123 12345 sales 100
6 234 56872 sales 100
7 234 76549 cost 0
8 234 88887 sales 0
9 234 66666 invoice 100
10 234 11111 invoice 100
;
run;
proc sql;
 create table want as 
  select *,case 
   when count(*) gt 2 then 'ERROR '
   else 'CORRECT'
   end as flag
   from have
   where type='sales'
   group by ID;
quit;

Re: How to perform similar vlookup function in dataflux and put conditions

Steelers_In_DC — Thu, 06 Aug 2015 15:12:22 GMT

I added a join to the solution above:

data one;

infile cards dsd;

input SINo ID$ status_number$ Type$;

cards;

1,123,45678,sales

2,123,34567,cost

3,123,78795,sales

4,123,98765,invoice

5,123,12345,sales

6,234,56872,sales

7,234,76549,cost

8,234,88887,sales

9,234,66666,invoice

10,234,11111,invoice

;

run;

data two;

infile cards dsd;

input SINo ID$ status_number$ Type$ percent;

cards;

1,123,45678,sales,0

2,123,34567,cost,100

3,123,78795,sales,100

4,123,98765,invoice,100

5,123,12345,sales,100

6,234,56872,sales,100

7,234,76549,cost,0

8,234,88887,sales,0

9,234,66666,invoice,100

10,234,11111,invoice,100

;

run;

proc sql;

create table output as

select a.sino,a.id,a.status_number,a.type,b.percent,

case

when count(*) < 3 then 'Correct'

else 'Error' end as Output

from one a left join

two b on

a.id = b.id and

a.status_number = b.status_number

where a.type = 'sales'

group by a.id

order by a.sino,a.id;

Re: How to perform similar vlookup function in dataflux and put conditions

Shah — Fri, 07 Aug 2015 08:29:03 GMT

Hi All,

I will try both the solutions. If i have face any difficulties, will get back. Thank you.

Regards,
Shaheen