Hi all
A small problem, not sure how to solve it (surely through proc sql...group by, but I cant figure it out, cant really figure out adding the extra row)
I have a table that looks like this:
Header 1 | Header 2 | Header 3 | Header 4 | Header 8 | Header 9 |
---|---|---|---|---|---|
1 | A | some data | some data | some data | some data |
1 | B | some data | some data | some data | some data |
1 | C | some data | some data | some data | some data |
2 | D | some data | some data | some data | some data |
2 | E | some data | some data | some data | some data |
3 | F | some data | some data | some data | some data |
3 | G | some data | some data | some data | some data |
4 | I | some data | some data | some data | some data |
I want to re-arrange Header 1 and Header 2:
Header 2 | Header 3 | Header 4 | Header 8 | Header 9 |
---|---|---|---|---|
1 | . | . | . | . |
A | some data | some data | some data | some data |
B | some data | some data | some data | some data |
C | some data | some data | some data | some data |
2 | . | . | . | . |
D | some data | some data | some data | some data |
E | some data | some data | some data | some data |
3 | . | . | . | . |
F | some data | some data | some data | some data |
G | some data | some data | some data | some data |
4 | . | . | . | . |
I | some data | some data | some data | some data |
Could anyone please advice me on how to do it?
Best wishes
This is not a table, it's a report. Therefore, don't use SQL.
Take a look at PROC TABULATE and REPORT.
Thank you
what I am showing here is actually a table in SAS, I need to reformat it to use in for further analysis
Best wishes
Please give an example of an analysis that relies on the order of the variables in the data set, especially where the names of the variables don't change.
Hello,
data have;
input Header_1 Header_2 $;
datalines;
1 A
1 B
1 C
2 D
2 E
3 F
3 G
4 I
;
proc sort data=have;
by Header_1;
run;
data want;
set have;
by Header_1;
_Header_2=Header_2;
if first.Header_1 then do;Header_2=Header_1;output;end;
Header_2=_Header_2;
output;
drop Header_1 _Header_2;
run;
Thank you Loko
The code still duplicates some values, when the code selects the row value from the Header1, the rest of row is filled by data and not empty as per my example
any further suggestions?
Regards
Hello,
data have;
input Header_1 Header_2 $ Header_3 Header_4 $;
datalines;
1 A 2 a
1 B 3 s
1 C 4 a
2 D 4 a
2 E 54 a
3 F 23 g
3 G 24 a
4 I 24 g
;
proc sort data=have;
by Header_1;
run;
proc sql noprint;
SELECT "'"||trim(name)||"'", name INTO :all_vars separated by ',', :all_vars_miss separated by ','
FROM SASHELP.VCOLUMN
WHERE libname = upcase("work") AND memtype = 'DATA'
AND memname = upcase("have") and name not in ('Header_1', 'Header_2');
QUIT;
data want;
set have;
if _N_=1 then
do;
declare hash ha(dataset:'have',multidata:'Y');
ha.definekey('Header_1', 'Header_2');
ha.definedata(&all_vars);
ha.definedone();
end;
by Header_1;
_Header_2=Header_2;
if first.Header_1 then do;ha.add();put _all_;call missing(&all_vars_miss);Header_2=Header_1;output;end;
rc=ha.find(key:Header_1, key:_Header_2);
ha.clear();
Header_2=_Header_2;
output;
drop Header_1 _Header_2 rc;
run;
Thank you everyone for your suggestions, especially thank you Loko for your time. Unfortunatly the code is still not working the way I wanted it. There are missing values in the new table now
Let us give the full example, the attached table is a typical output table obtained by ods output from a proc logistic.
What I am trying to do is to convert the structure of the table to be compatible with the one needed to build a forest plot according to the excelent example given here
http://blogs.sas.com/content/graphicallyspeaking/2012/09/30/forest-plot-with-subgroups/
The exact code and the necessary table structure is available here:
http://blogs.sas.com/content/graphicallyspeaking/files/2012/09/ForestPlot_931.txt
As this is something I will be doing several times, I al trying to code the conversion of the OR table to the one needed for the forest plot.
I really appreciate all the help and suggestions
Best wishes
Hi:
If you look at the program for SAS 9.3, you will see that the data is created with dots or periods as placeholders for the indents used in the final report. The dots are then replaced with the hex character for a non-breaking space.
You could take your data and generate the same type of data as used by the program using character functions and concatenate to make the sub-grouping variable.
cynthia
Thank you
However, if you look at the code you see that the table forest2 is created without dots etc, and that is the format I am trying to convert my table to
Best wishes
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.