BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,



1 XYZ 658 255.20 107620.11 9169.86 76384.72

2 XYZ 658 255.20 107620.11 9169.86 76384.72

3 XYZ 658 255.20 107620.11 9169.86 76384.72

4 XYZ 658 255.20 107620.11 9169.86 76384.72



When I run a code I get my result in the above fashion. As you see, the observations are same and its redundancy to have the rows repeating. I would rather like to see the report as follows.



1 XYZ 658 255.20 107620.11 9169.86 76384.72



Please tell me how I can squeeze it using proc print or any other proc



Thanks,
6 REPLIES 6
LinusH
Tourmaline | Level 20
proc sort noduprecs
(if you first column is observation number and not a data column)

/Linus
Data never sleeps
Cynthia_sas
SAS Super FREQ
Hi:
It looks like you have duplicate observations in your data. If this is an acceptable condition, then you have several choices:
1) Use PROC SORT to create a temporary data set that does not hold any of the duplicate observations and then use PROC PRINT for your REPORT;
2) Use PROC REPORT, but define ALL the variables as GROUP variables so that all the duplicate rows collapse into 1 row (where every column is exactly the same)
3) Use PROC REPORT, but add up all the numeric variables (assumes that the duplicate observations are valid.

For example, given this data with the variables NAME, VAR1 and VAR2:
[pre]
alan 1 1
alan 1 1
alan 1 1
bob 2 2
bob 2 2
bob 2 2
carl 3 3
carl 3 3
carl 3 3
[/pre]

I can produce these results:
[pre]
Proc PRINT with Dups removed by Proc SORT

Obs name var1 var2

1 alan 1 1
2 bob 2 2
3 carl 3 3

******************************************************
Proc REPORT with all variables as GROUP

Number
name var1 var2 of Obs
alan 1 1 3
bob 2 2 3
carl 3 3 3

******************************************************
Proc REPORT with Sum of Numeric variables

Number
name var1 var2 of Obs
alan 3 3 3
bob 6 6 3
carl 9 9 3

[/pre]

It really depends on what the significance of the duplicate observations are and how you want them to be treated. (There's probably a PROC SQL SELECT DISTINCT solution for removing dups....but this should be enough to get you started).

cynthia

[pre]
*** the code;

data mydata;
infile datalines;
input name $ var1 var2;
return;
datalines;
alan 1 1
alan 1 1
alan 1 1
bob 2 2
bob 2 2
bob 2 2
carl 3 3
carl 3 3
carl 3 3
;
run;

proc sort data=mydata nodupkey out=data2;
by name var1 var2;
run;

ods listing close;
options center nodate nonumber;
ods html file='c:\temp\nodups.html' style=sasweb;
**1) remove dups with PROC SORT and then PRINT;
proc print data=data2;
title 'Proc PRINT with Dups removed by Proc SORT';
run;

**2) Use PROC REPORT and define all vars as GROUP;
proc report data=mydata nowd;
title 'Proc REPORT with all variables as GROUP';
column name var1 var2 n;
define name /group;
define var1 / group;
define var2 / group;
define n / 'Number of Obs';
run;

**3) Use PROC REPORT and define some vars as GROUP, SUM the others;
proc report data=mydata nowd;
title 'Proc REPORT with Sum of Numeric variables';
column name var1 var2 n;
define name /group;
define var1 / sum;
define var2 / sum;
define n / 'Number of Obs';
run;
ods _all_ close;


[/pre]
deleted_user
Not applicable
Thanks a lot for your feedback.

I've two data sets



DS1



Var1 var2 var3 var4

X 123 2345 324

Y 234 3245 234

Z 345 3456 314



DS2



Var1 var5

X .98

X .87

X .86

Y .64

Y .65

Y .64

Z .09

Z .34

Z .87



I had merged these two files to get the resultant output with var6 var7 var8 (including 1 -5 ), that had to calculate Var1, 2, 3 with 5. During this operation I came across such a situation that repeated the o/p values.

for this report it does not include var5 so I could suppress it with no repeated obs.

I need to create a report that includes var5 with all the values but also summarize the other vars with single obs.

I would appreceate if you can come up with a solution to this.
deleted_user
Not applicable
I want the report to look like this:

Var1 var2 var3 var4 var5 var6 var7
X 123 2345 324 0.98 120.54 2298.1
0.87
0.86
Y 234 3245 234 0.64 149.76 2076.8
0.65
0.64
Z 345 3456 314 0.09 31.05 311.04
0.34
0.87

Currently I’m getting it this way

Var1 var2 var3 var4 var5 var6 var7
X 123 2345 324 0.98 120.54 2298.1
X 123 2345 324 0.87 120.54 2298.1
X 123 2345 324 0.86 120.54 2298.1
Y 234 3245 234 0.64 149.76 2076.8
Y 234 3245 234 0.65 149.76 2076.8
Y 234 3245 234 0.64 149.76 2076.8
Z 345 3456 314 0.09 31.05 311.04
Z 345 3456 314 0.34 31.05 311.04
Z 345 3456 314 0.87 31.05 311.04


Thanks,
SN Message was edited by: liv4sas
Cynthia_sas
SAS Super FREQ
Hi:
Neither PROC PRINT nor PROC REPORT will produce a report that looks like this:
[pre]
Var1 var2 var3 var4 var5 var6 var7
X 123 2345 324 0.98 120.54 2298.1
0.87
0.86
Y 234 3245 234 0.64 149.76 2076.8
0.65
0.64
Z 345 3456 314 0.09 31.05 311.04
0.34
0.87
[/pre]

You may want to read up in the documentation on Data Step reporting, particularly, DATA _NULL_. If you want to write to the LISTING window, then investigate the program examples for FILE PRINT with PUT statements. If you want to write to ODS HTML, RTF or PDF destinations, then investigate the program examples for FILE PRINT ODS with PUT _ODS_.

cynthia
yonib
SAS Employee
You can use proc sql:

proc sql;
create table /*name of the table*/ as
select distinct *
from /*name your data came from*/ ;
quit;

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
  • 6 replies
  • 954 views
  • 0 likes
  • 4 in conversation