BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nottp
Fluorite | Level 6
I have data something like this
f1 f2 f3 f4 f5 f6 ... f500
1 2 3 8 4 6 ... 4
I want data
1 2 3
8 4 6
...
How to write sas code? Thank you.
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

First solution: use a macro loop to create multiple statements that dissect the variables:

data have;
input f1 :yymmdd10. f2 f3 :$10. f4 :yymmdd10. f5 f6 :$10.;
cards;
2018-07-09 3 XXXXXXX 2018-07-10 5 YYYYYYY
;
run;

%macro create_datastep(maxvar);
data want;
set have;
format
  f_1 yymmddd10.
  f_2 5.
  f_3 $10.
;
%do i = 1 %to &maxvar./3;
  %let i1 = %eval(&i. * 3 - 2);
  %let i2 = %eval(&i. * 3 - 1);
  %let i3 = %eval(&i. * 3);
f_1 = f&i1.;
f_2 = f&i2.;
f_3 = f&i3.;
output;
%end;
keep f_1 f_2 f_3;
run;
%mend;

%create_datastep(6)

A probably more "elegant" solution would create three arrays in similar %do loops, and then iterate in a data step do loop.

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

Define an array for f1-f500.

Loop over the array with do i = 1 to 500.

In the loop, do

array{mod(i,3)} = array{i};
if mod(i,3) = 0 then output;

Keep f1-f3.

Add some code to deal with the last group (since 500 is not a multiple of 3)

nottp
Fluorite | Level 6
If data type in f column are difference. How can i do?
nottp
Fluorite | Level 6
Have another solution? Use while loop etc.
Kurt_Bremser
Super User

The problem is not the "how", it's the "what". If f1, f4 and f7 have different attributes, you can't (reliably) store the values in the same column. 

nottp
Fluorite | Level 6
Ok real sample data below
f1 f2 f3 f4 f5 f6 ... f567 format is f1, f4, f7 = date, f2, f5, f8 = number and f3, f6, f9 = text repeat this until f567 i want arrang data follow my topic. Thank your for advise.
Kurt_Bremser
Super User

First solution: use a macro loop to create multiple statements that dissect the variables:

data have;
input f1 :yymmdd10. f2 f3 :$10. f4 :yymmdd10. f5 f6 :$10.;
cards;
2018-07-09 3 XXXXXXX 2018-07-10 5 YYYYYYY
;
run;

%macro create_datastep(maxvar);
data want;
set have;
format
  f_1 yymmddd10.
  f_2 5.
  f_3 $10.
;
%do i = 1 %to &maxvar./3;
  %let i1 = %eval(&i. * 3 - 2);
  %let i2 = %eval(&i. * 3 - 1);
  %let i3 = %eval(&i. * 3);
f_1 = f&i1.;
f_2 = f&i2.;
f_3 = f&i3.;
output;
%end;
keep f_1 f_2 f_3;
run;
%mend;

%create_datastep(6)

A probably more "elegant" solution would create three arrays in similar %do loops, and then iterate in a data step do loop.

nottp
Fluorite | Level 6
thank you. i will try.
Kurt_Bremser
Super User

It's of course best to fix such an issue when reading from an external file:

data want2;
input f_1 :yymmdd10. f_2 f_3 :$10. @@;
format f_1 yymmddd10.;
cards;
2018-07-09 3 XXXXXXX 2018-07-10 5 YYYYYYY
;
run;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1251 views
  • 0 likes
  • 2 in conversation