Skip over data in reading raw data

[sap1958]

data del6;
length Rank 3 Company $20 Country $25 Revenue 8 Expenses 8 NetIncome 8 Employees 8;
infile 'v:\pcsas_export_files\Delimiter6.txt' dlm = '[ ] &';
input Rank Company Country Revenue Expenses NetIncome Employees;
format Revenue Expenses NetIncome dollar14.2;
run;

sample output
1 Johnson & Johnson United States 63,747.0[20] 12,949.0 118,700
2 Pfizer United States 48,296.0[21] 8,104.0 81,800

I am trying to eliminate the & and bring Johnson and Johnson together and I want to eliminate the numbers after the revenue variable (ie 63,747.0[20]) In this case eliminate the [20]

The above script is moving the [20] and other numbers into their own field and moving Johnson into a separate field.

 

[jj72uk]

Hi Sap,

If you dont want the Expenses NetIncome Employees vars then why bring them in in the first place? If there's a reason you could always do a drop/keep to loose them.

As for your "&" I'd suggest you have a look at the INDEX function.

This allowed you to search a variables string and basically do a find and replace.

 

[sap1958]

1 Johnson & Johnson United States 63,747.0[20] 12,949.0 118,700
2 Pfizer United States 48,296.0[21] 8,104.0 81,800

The above sample is actually the raw data file not the output. My mistake in my last email for telling you that. When I bring in the data to SAS I want to not bring in the [20] or [21]. I also want to ensure that Johnson & Johnson comes in together in one field even it means elimination of the &.