BookmarkSubscribeRSS Feed
Shah
Obsidian | Level 7

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

3 REPLIES 3
Ksharp
Super User

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;
Steelers_In_DC
Barite | Level 11

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;

Shah
Obsidian | Level 7

Hi All,

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

Regards,
Shaheen

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1056 views
  • 0 likes
  • 3 in conversation