BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Abhi1212
Obsidian | Level 7

Hello,

I have an excel file with 3 columns. Say A,B and C. Now the C column as some values like PPA249394893,PPA3949343904, CPA239483984,DEF948594 etc.. Now i need to remove the value which as prefix PPA from Column C. How to do that in SAS EG ? i have imported the file as below. I need the records which i removed(PPA Prefix records) as well in separae dataset. Please help me to proceed further.

 

PROC IMPORT OUT =Test

datafile= "/Test/test_file.xlsx"

DBMS = XLSX

REPLACE;

GETNAMES = YES;

RUN;

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

When you send the mail using filename email, simply do this in the data step:

data _null_;
filename emailout; /* or whatever file reference you used in filename email */
/* as one of the options in the infile statement, you can attach the want2 dataset, using its physical path */
put "&total_read observations were read.";
put "&total_output were accepted.";
put "&total_excluded were rejected.";
run;

As an alternative to attaching, you could store the want2 dataset in a library that is accessible to users, and simply add the complete dataset name as text to the email. That keeps mails small (to the pleasure of your mail administrators).

View solution in original post

12 REPLIES 12
Abhi1212
Obsidian | Level 7
Thanks for your reply Kurt. Meanwhile, Control totals should also be captured including: Totals records read, total invalid records excluded, total records output. How that can be done?
Kurt_Bremser
Super User

@Abhi1212 wrote:
Thanks for your reply Kurt. Meanwhile, Control totals should also be captured including: Totals records read, total invalid records excluded, total records output. How that can be done?

Count during the step, and then save to macro variables for further use:

data want1 want2;
set test end=done;
retain
  count_out 0
  count_x 0
;
if substr(c,1,3) = 'PPA'
then do;
  output want2;
  count_ex + 1;
end;
else do;
  output want1;
  count_out + 1;
end;
if done
then do;
  call symputx('total_read',put(_n_,best.));
  call symputx('total_output',put(count_out,best.));
  call symputx('total_excluded',put(count_ex,best.));
end;
drop count_out count_ex;
run;
Abhi1212
Obsidian | Level 7
ERROR 22-322: Syntax error, expecting one of the following: ;, CANCEL, PGM.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

I get this error
Abhi1212
Obsidian | Level 7
GOPTIONS NOACCESSIBLE;
________
22
202

ERROR 22-322: Syntax error, expecting one of the following: ;, CANCEL, PGM.

ERROR 202-322: The option or parameter is not recognized and will be ignored.
Abhi1212
Obsidian | Level 7

Sorry Kurt i udnerstood what you asked for. I rectified the error. I missed a ; in program that was the issue.

I saw the output i got an extra column named count_X with o values. But i need something as in the log.

 

I need to send the below part in an email with invalid records file attached to it.

 

NOTE: There were 3508 observations read from the data set WORK.TEST.

NOTE: The data set WORK.WANT1 has 3501 observations and 5 variables.

NOTE: The data set WORK.WANT2 has 7 observations and 5 variables.

 

Kurt_Bremser
Super User

When you send the mail using filename email, simply do this in the data step:

data _null_;
filename emailout; /* or whatever file reference you used in filename email */
/* as one of the options in the infile statement, you can attach the want2 dataset, using its physical path */
put "&total_read observations were read.";
put "&total_output were accepted.";
put "&total_excluded were rejected.";
run;

As an alternative to attaching, you could store the want2 dataset in a library that is accessible to users, and simply add the complete dataset name as text to the email. That keeps mails small (to the pleasure of your mail administrators).

lakshmi_74
Quartz | Level 8

Is this solution you are looking?

data test;
input C$ A B;
datalines;
PPA249394893 12 34
CPA239483984 23 45
DEF948594 56 67
PPA249394893 90 10
;
run;
data ppa_test(drop=new_c) without_ppa(drop=c);
set test;
if upcase(substr(c,1,3))='PPA' then
do;
new_c=substr(c,3); output without_ppa;
end;
else output ppa_test;
run;

Abhi1212
Obsidian | Level 7

Lakshmi,

My input values are given through excel. So first i should import and then i should play around. Also your code is seem to be more lengthy. Just IF statement will solve. Thanks for your response.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1674 views
  • 3 likes
  • 3 in conversation