SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to perform similar vlookup function in dataflux and put conditions

Reply
Contributor
Posts: 21

How to perform similar vlookup function in dataflux and put conditions

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.

  1. To populate percent field in the 1st file
  2. 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 NoIDstatus_numberType
112345678sales
212334567cost
312378795sales
412398765invoice
512312345sales
623456872sales
723476549cost
823488887sales
923466666invoice
1023411111invoice

2nd File
SI NoIDstatus_numberTypePercent
112345678sales0
212334567cost100
312378795sales100
412398765invoice100
512312345sales100
623456872sales100
723476549cost0
823488887sales0
923466666invoice100
1023411111invoice100

Output

SI NoIDstatus_numberTypePercentOuput
112345678sales0Error
312378795sales100Error
512312345sales100Error
623456872sales100Correct
823488887sales0Correct

Regards,

Shaheen

Super User
Posts: 10,023

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

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;
Valued Guide
Posts: 860

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

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;

Contributor
Posts: 21

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

Posted in reply to Steelers_In_DC

Hi All,

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

Regards,
Shaheen

Ask a Question
Discussion stats
  • 3 replies
  • 324 views
  • 0 likes
  • 3 in conversation