PROC Trasnpose/query builder in SAS Enterprise Guide

Question

After I run Transpose in query builder, i get '.' for empty fields. Is there a way to avoid these '.' ? I can remove those in the next step by adding a case statement but doing this for more than 100 columns won't be a good idea.

123019  1   .   .   .
166584  .   1   .   .
171198  .   .   1   .
285703  .   .   .   1
309185  .   .   .   2
324756  .   .   .   1
335743  .   .   .   .
348340  .   .   .   .

Please help.

Thanks

missing numeric values in SAS are represented by '.'. When you say you want to get rid of them do you mean get rid of rows where all 100 columns have missing values? For ex: in the above ex. row_id: 335743 & 348340 would be deleted as they have all columns set to '.' — , Jul 12 '13 at 14:50
want to get rid of these "."..... either the fields should just be blank or "0". Thanks — learnlearn10, Jul 12 '13 at 14:53
I strongly suggest (again, I think) writing your own code rather than using Query Builder. SAS syntax is not hard to learn and will give you far more power than query builder. — Joe, Jul 12 '13 at 15:37

isJustMe · Answer 1 · 2013-07-12T15:44:43.673

0

You can use this code:

data myData;
set myData;
array a(*) _numeric_;
do i=1 to dim(a);
if a(i) = . then a(i) = 0;
end;
drop i; 
run;

Or you can just run all the steps and add this at the bottom of your datastep:

array a(*) _numeric_;
    do i=1 to dim(a);
    if a(i) = . then a(i) = 0;
    end;
    drop i; .

BTW this will replace the . to zeros, the "." represents a missing value in SAS, you can replace the 0 on the code that I provided for any other value you want to show instead of .

EDIT:given your inputs the code should be like this:

PROC SORT DATA=ABC 
OUT=ABC1 ; 
BY EMP; 
RUN; 
PROC TRANSPOSE DATA=ABC1 OUT=ABC2 NAME=Source LABEL=Label; 
BY EM; 
ID VC; 
VAR FQ; 
/* ------------------------------------------------------------------- End of task code. ------------------------------------------------------------------- / 
RUN; QUIT; 

/* Start of custom user code. */
data ABC2;
set ABC2;
array a(*) _numeric_;
do i=1 to dim(a);
if a(i) = . then a(i) = 0;
end;
drop i; 
run;

edited Jul 12 '13 at 15:44

answered Jul 12 '13 at 14:53

isJustMe

5,452
2
31
47

PROC SORT DATA=ABCD OUT=DEF ; BY EMP; RUN; PROC TRANSPOSE DATA=DEF OUT=EFG NAME=Source LABEL=Label ; BY EMP; ID VC; VAR FQ; Data EFG; Set EFG; array FQ(*) _numeric_; do i=1 to dim(FQ); if FQ(i) = . then FQ(i) = 0; end; drop i; – learnlearn10 Jul 12 '13 at 15:00
I tried inserting the code in Transpose(via query builder). I got this error : ERROR 180-322: Statement is not valid or it is used out of proper order. 54 SET=EFG ___ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 55 ; 56 array FQ(*) _numeric_; _____ 180 – learnlearn10 Jul 12 '13 at 15:20
PROC SORT DATA=ABC OUT=ABC1 ; BY EMP; RUN; PROC TRANSPOSE DATA=ABC1 OUT=ABC2 NAME=Source LABEL=Label ; BY EM; ID VC; VAR FQ; /* ------------------------------------------------------------------- End of task code. ------------------------------------------------------------------- */ RUN; QUIT; /* Start of custom user code. */ DATA=ABC2; SET=ABC3 ; array FQ(*) _numeric_; do i=1 to dim(FQ); if _FQ(i) = . then _FQ(i) = 0; end; drop i; run; – learnlearn10 Jul 12 '13 at 15:33
I am new to this programming so let me know if I did a blunder. – learnlearn10 Jul 12 '13 at 15:33
Sounds like your data statement and set statement are reversed. – Joe Jul 12 '13 at 15:38
hey don't worry! we are here to learn, see my updated answer and let me know if it works for you – isJustMe Jul 12 '13 at 15:39
changed the set to ABC2 and FQ to a but still getting the same error......52 /* Start of custom user code. */ 53 DATA=ABC2; ____ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 54 SET=ABC2 ___ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 55 ; 56 array a(*) _numeric_; _____ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 57 do i=1 to dim(a); __ 180 – learnlearn10 Jul 12 '13 at 15:58
Data steps begin with `data datasetforoutput; set datasetforinput; ... run;`, no equal signs. I highly suggest reading one of the basic SAS programming books ("The Little SAS Book" is a good one, it's cheap and quick) or papers. – Joe Jul 12 '13 at 16:13
Will definitely read that... thanks. Is this book for programming as well ? – learnlearn10 Jul 12 '13 at 16:38
That book is for SAS programming specifically (not other sorts), though the basic concepts can be helpful. SAS is a 4GL (ie, not a general language like c++) so programming in it is both easy, and not exactly like any other language since it is somewhat purpose-built and highly functional. – Joe Jul 12 '13 at 16:51

score 0 · Accepted Answer · answered Jul 12 '13 at 15:36

0

Dot (missing) is identical to "blank" in SAS. If you're actually printing the data out, you can use the statement:

options missing=' '; *or 0 or any other character;

That will be shown for missing (null/blank) values. In some contexts that may not be preserved, in which case you either use a data step to convert to zero, or use PROC STDIZE:

proc stdize data=mydataset missing=0 reponly;
run;

which may be faster/easier to code, if you have SAS/STAT licensed.

answered Jul 12 '13 at 15:36

Joe

62,789
6
49
67

I added proc stdize data=ABC2 missing=0 reponly; run; in the query builder, it didn't do anything. But if write this as a separate program it works fine. – learnlearn10 Jul 12 '13 at 15:48

PROC Trasnpose/query builder in SAS Enterprise Guide

2 Answers2